Feb 11 2021 01:04 AM
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.
Feb 11 2021 01:52 AM
=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,
Feb 11 2021 01:56 AM
@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.
Feb 11 2021 02:24 AM
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.
Feb 11 2021 02:54 AM
@Riny_van_EekelenI think that the problem must be that my data is on a separate tab.
Feb 11 2021 02:58 AM
Couple of comments:
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.
Feb 11 2021 03:44 AM
Solution
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!
Feb 11 2021 05:21 AM
@Paul_wfp , great to know you sorted this out, glad to help
Feb 11 2021 03:44 AM
Solution
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!