SOLVED

Sumifs Logic citeria

Occasional Contributor

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

@Paul_wfp 

 

=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,

 

 

 

 

@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.

Screenshot 2021-02-11 at 10.53.56.png

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.

 

I will keep trying. Thank you for your reply.

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

@Paul_wfp 

Couple of comments:

First, limit is much higher

image.png

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

@Sergei Baklan 

 

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

 

Perfect answer thank you both for your help!

@Paul_wfp , great to know you sorted this out, glad to help