SOLVED

Sumifs Logic citeria

Occasional Contributor

Sumifs Logic citeria

I need to get a total after applying 3 logic criteria.  So I started with this:

=SUMIFS(Orders!AE:AE,Orders!G:G,"Spencer",Orders!H:H,"Euros",Orders!AI:AI,"January") this returns 0.00

if I delete Orders!G:G,"Spencer", it returns a value but the wrong value as i need all 3 logic tests.

After extensive reading it appears you are limited to 2 logic tests in  SUMIFS so then I tried this:

=IF(Orders!G:G="Spencer",SUMIFS(Orders!AE:AE,Orders!H:H,"Euros",Orders!AI:AI,"January")) which returns #Spill! I know I am on the right track  but cant quite get there would love some help.

7 Replies

Re: Sumifs Logic citeria

=IF(Orders!G:G="Spencer",SUMIFS(Orders!AE:AE,Orders!H:H,"Euros",Orders!AI:AI,"January"),"0") Still returns #Spill!

=IF(Orders!G:G="Spencer"+SUMIFS(Orders!AE:AE,Orders!H:H,"Euros",Orders!AI:AI,"January"),"0") does not like that at all.

The answer I am looking for has not got to be specific data but a formula that allows 3 logic criteria for a sum to be done. Now on my second day of this,

Re: Sumifs Logic citeria

@Paul_wfp Am not aware of the fact that you can only have two criteria in SUMIFS. Created a simplified mock-up of your schedule to demonstrate that it should work as you initially intended. See picture below.

Re: Sumifs Logic citeria

HI@Riny_van_Eekelen that is what I had hoped and expected to work but it does not.

I read that you can have up to 127 pairs but only 2 logic statements trying to find the link to show you.

I can see that yours works I can only think that it may be something to do with the data.

Re: Sumifs Logic citeria

@Riny_van_EekelenI think that the problem must be that my data is on a separate tab.

Re: Sumifs Logic citeria

First, limit is much higher

SUMIFS function - Office Support (microsoft.com)

Next, I'd use dynamic ranges if not structured tables instead of entire columns.

But in general your first formula is correct. Why it returns wrong answer - too hard to say without the file. It could be extra space or non-printable character for "Spencer", or texts instead of numbers in AE column, whatever.

Second formula =IF(Orders!G:G="Spencer",SUMIFS(... tries into all million rows in column where you enter it some calculation by sumifs, of false otherwise. There is not enough room for that million of values, thus SPILL error.

best response confirmed by Paul_wfp (Occasional Contributor)
Solution

Re: Sumifs Logic citeria

Cant thank you enough exactly that "It could be extra space or non-printable character for "Spencer""