Sumif not working

Question

Hi,

I want to have a sumif formula that has criteria relating to a date and customer name and "deposit". When i use sumif and two of the above criteria it works but when all 3 are together it doesnt and i can explain why. Please see attached images and any help would be great.

Thanks,

Joe

Answer 1

i tried rearranging and its coming up blank.

Yes, an incredible misdirection. I would ignore people who waste my time like that.

As for the other comment, yes there is matching criteria, if i split this between two sumifs statement then both work e.g if i search by customer name and date it works, if i search customer name and "deposit" it works but not together and i have matching criteria, i am lost at this stage.

What Rory is correctly trying to make you understand is a situation like the following:

Row 1 matches the criteria for column N and column E.

Row 2 matches the criteria for column N and column A.

But no row matches all three criteria because the value in E2 is "withdrawal", not "deposit", and A1 is less than, not greater than, 500.

The problem in your file might be more subtle, and looks can be deceiving. Screenshots of the data (which you have not shown!) might not be sufficient for us to help you.

If you need further assistance, share a view-only link to an Excel file that demonstrates the problem. Upload the Excel file to a file-sharing website such as box.net/files, dropbox.com, onedrive.live.com, etc.

Note that I say "an" Excel file -- not necessarily the original Excel file, if it contains proprietary data.

Answer 2

The fact that it works for the two pairs of criteria does not mean that there are data that match all three criteria at once. Have you verified that data exists that matches all three at once (e.g with an autofilter)?

Answer 3

Hi jeovany,

I appreciate the help so thank you but i tried rearranging and its coming up blank. As for the other comment, yes there is matching criteria, if i split this between two sumifs statement then both work e.g if i search by customer name and date it works, if i search customer name and "deposit" it works but not together and i have matching criteria, i am lost at this stage.

Thanks,

Joe

Answer 4

Are you sure there actually are items that match all three criteria at once?

Answer 5

Hi J

Please, try it by changing the order of the parameters in the formula

=SUMIFS(Ath..S:S, Ath..N:N, criteria,Ath..E:E, criteria, Ath..A:A, criteria)

Do let me know if you need more help

Regards from Limerick

Jeovany