SUMIFS returning 0

[ninemuses0]

I have been struggling with the SUMIFS function.

I have a table of credit card charges that I am trying to use to input into a budget spreadsheet. I want the sum of charges that are in a certain category (Transportation, Shopping, Groceries, etc.) and a certain date (I've formatted the date to be "Aug-16" so that I can enter the it as a text condition, because I couldn't figure out how to limit it to a particular month rather than a specific day.)

Right now my SUMIFS formula is: =SUMIFS(ItemizedCharges[Amount],ItemizedCharges[Date], "Oct-16",ItemizedCharges[Category],"Cash")

(ItemizedCharges being the name of the table with the charges and Amount, Date and Category being the column titles in the table.)

The issue is that it is returning zero. When I remove one of the criteria, it successfully pulls a correct number, but when I add a second condition is pulls zero. I am positive that there are charges within the given month in the categories that I am looking for, but nothing seems to work. (So both =SUMIFS(ItemizedCharges[Amount],ItemizedCharges[Date], "Oct-16") and =SUMIFS(ItemizedCharges[Amount],ItemizedCharges[Category],"Cash") are working.

Can anyone help?

[AliGW]

Welcome to the forum!

Will you please attach a sample Excel workbook?

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[joeu2004]

=SUMIFS(ItemizedCharges[Amount],ItemizedCharges[Date], "Oct-16",ItemizedCharges[Category],"Cash")
[....]
The issue is that it is returning zero. When I remove one of the criteria, it successfully pulls a correct number, but when I add a second condition is pulls zero.

Ergo, either there are rows where one or the other condition is true, but no rows where both conditions are true; or in the rows where both conditions are true, the value of ItemizedCharges[Amount] is (numeric?) text, not a numeric value. SUMIFS ignores text, even if it appears to be numeric.

[EDIT] Or ItemizedCharges[Amount] is indeed zero in all rows where both conditions are true.

You can use Data > Filter to determine if there are rows where both conditions are true.

If there are any, you can use ISTEXT to determine if they are text instead of numeric values.

[ninemuses0]

@joeu2004

Thank you for the advice! I have already ensured that the entire "Amount" column that I'm pulling the sum from is formatted as a numeric value, and the two single criteria SUMIFS working seems to confirm that. I've also manually checked that there are multiple rows in which both criteria are true -- I've even subbed in different Categories and Months to ensure there isn't a spelling error or something else minor holding it up. So it doesn't seem to be either of those issues.

[ninemuses0]

My example file is attached.

[AliGW]

I have just opened your file, enabled editing and this is what I see (I have done nothing to it - this is on a Windows 10 PC using Excel 2016 (365 subscription)):

Excel 2016 (Windows) 32 bit

	E	F
2	Attempted SUMIFS Forumlas
3	Both Criteria	134.2
4	First Criteria	967.45
5	Second Criteria	1334.2

Sheet: Data

[joeu2004]

My example file is attached.

Change the formula to:

Please Login or Register  to view this content.

That returns 435.39, which matches the sum in the status bar if I filter column A for "August" and column B for "Cash".

You see "Aug-16" in column A due to cell formatting. But SUMIFS compares the actual dates, not the cell appearance.

PS.... SUMIFS(...,"Aug-16") returns a non-zero value because "Aug-16" is interpreted as 8/16/2016. To demonstrate, enter =--"Aug-16" formatted as Short Date. Rows 158, 159 and 199 have amounts on 8/16/2016, which total 42.29, the same value that SUMIFS(...,"Aug-16") returns. The categories are not "Cash".

PPS.... In order to filter for 8/16/2016, we need to use the Between option. The Equals option looks at the cell appearance (case-insensitive). :-(

[ninemuses0]

Thank you! @joeu2004, this has worked! It must have been the date issue. I can't tell you how grateful I am.

I am still puzzled by why on earth my cell is returning 0 for "Aug-16" rather than the sum for August 16th, but it seems to be of little importance now.

Thanks again!

[joeu2004]

Thank you! @joeu2004 [....] I am still puzzled by why on earth my cell is returning 0 for "Aug-16" rather than the sum for August 16th, but it seems to be of little importance now.

You're welcome!

Perhaps you didn't see my PS and PPS. The only amounts on Aug 16 (2016) have categories that are not "Cash". Hence, there are no amounts that are both on Aug 16 and with category "Cash".

But I assumed that by "Aug-16", you mean any date in Aug 2016, right? That is what "Aug-16" means in column A.

[ninemuses0]

You're welcome!

Perhaps you didn't see my PS and PPS. The only amounts on Aug 16 (2016) have categories that are not "Cash". Hence, there are no amounts that are both on Aug 16 and with category "Cash".

But I assumed that by "Aug-16", you mean any date in Aug 2016, right? That is what "Aug-16" means in column A.

Ah, I did not see your note. You are correct and that all makes sense. Thanks again!

[christjjan08]

I know it's been some four years since this thread - but I've been having issues with the same thing on Google Sheets. Your replies helped me figure out the issue. Thank you!!!

[AliGW]

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

[The_Snook]

I think you've jumped the gun Ali, christjjan08 was just saying thank you after using this thread to help him solve a similar problem.

Snook

[AliGW]

Not at all - if Christian needs any further help, he knows to open his own thread.