SUMIFS Formula not giving desired result

[lukemelville]

Hi,

I am a using Excel 2013 and I am having trouble understanding why a formula I am working with isn't working.

The problem: So I am creating an Excel Worksheet that gets income and expense data from a MySQL database, imports it into tables and then formulas determine expenses and income for a given year. I have linked up MySQL and that's all working fine but my problem is with the expenses calculation.

The formula requires data from two sheets:

• Annual Expenses 2013: contains the calculations based on the Expenses sheet. It creates a table based on the categories made ([Expense Name]) in the MySQL database and then calculates from the Expenses sheet what expenses were made (expenses_totalsum) in that category given they were made in the a year specified in a cell (annual_expense_year).
• Expenses: contains the data imported from the MySQL database. The fields I am working with in this sheet are the category the expense is filed in (expenses_category_name) and the year the expense was made (expense_year).

So the formula I used in the first place was: =SUMIF(expenses_category_name,[Expense Name],expenses_totalsum). This would search through the category name in the Expenses sheet, match it with the Expense Name in the Annual Expenses 2013 sheet and return the SUM of said values that matched those criteria from the expenses_totalsum column in the Expenses sheet.

This all worked fine but now I need to introduce the year factor. So I need to achieve the same thing except any value it finds must also search the Expenses sheet for the year the expense was made and match it to the year set in the Annual Expenses 2013 Sheet.

This is the formula I have tried amongst other things but I can see where I have gone wrong: =SUMIFS(expenses_totalsum,expenses_category_name,[Expense Name],expenses_year,annual_expense_year) this just returns a value of 0.
Just a little extra info: the annual_expense_year value is a cell with the year in it within the Annual Expenses 2013 sheet. The expense_year is a column of data within the Expenses sheet (each expense is assigned a date and this column just displays the year of that date).

I can upload the worksheet if anyone want's it I just don't know if the data will also move with the sheet given the MySQL is on a localhost (for now).

Thanks for any help and I hope I have explain it well. I tried my best but it's a little complex.

[FDibbins]

Hi and welcome to the forum

the 1st thing I would check would be to make sure that the dates are in the same format on both sides. You may find that what looks like just a year number (2013), may in fact be a full date, just formatted to show just the year - it may look like 10/12/2013 for instance if formatted differently.

On the other hand, 1 of the date "sets" may be just that, a 4-digit number.

Its really hard to say for sure what the problem is without seeing a sample of what you are working with To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[Norie]

If the expense_year column contains date values but only displays the year then they aren't going to match the year in the annual_expense_year cell.

There are probably a few ways to handle it, for example use criteria to find dates in the expense year that are between the start and end of the year in annual_expense_year.

Can you upload an example workbook?

Click on GO ADVANCED and use the paperclip icon to open the upload window.

[lukemelville]

Thanks for the responses @FDibbins and @Norie. I think you both are onto something with the expense_year value.
I uploaded and example for you to look at. Don't judge for the way it's laid out so far. I am getting the formulas and that right before I make it look good and flow well

[Norie]

Try this.

Please Login or Register  to view this content.

By the way, there's a couple of things that might make things a bit easier.

For example, you could use the MySQL query to return the year with the YEAR function, eg YEAR([created])

Also, the data you have is in tables so you could use structured table referencing rather than named ranges.

With structured table referencing you could use something like this

Please Login or Register  to view this content.

Note, I changed the table name to ExpTable - Table_Query_from_TechCloudEnt__LocalDatabase3 was a bit of a handful.

[lukemelville]

Thanks for your help @Norie. That formula is perfect. I have noted your suggestion to use structured table referencing. I am just playing around with Excel at the moment. Working on a bigger idea that might work something like this.
Have a good evening.

[FDibbins]

Happy to help and thanks for the feedback