• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

SUMIF only taking notice of first criteria ?

C

cheeseboy

New Member
Hi all,

I wonder if you wonderful people might be able to help me. I've got a sumif which includes multiple values for each criteria as follows:

=SUMIFS('All Apps'!$S:$S,'All Apps'!$C:$C,E1,'All Apps'!$I:$I,{"ICG","Local"},'All Apps'!$K:$K,{"Retire","Migrate"})

The only problem with this, is that Excel is only taking notice of the first value in each criteria so what it actually reports on is :

=SUMIFS('All Apps'!$S:$S,'All Apps'!$C:$C,E1,'All Apps'!$I:$I,{"ICG"},'All Apps'!$K:$K,{"Retire"})

Can anyone tell me where I'm going wrong please ? I've been staring at this for 4 hours now :(
Thanks in advance
Ian.
 
Hi Ian ,

Write your formula as :

=SUM(SUMIFS('All Apps'!$S:$S,'All Apps'!$C:$C,E1,'All Apps'!$I:$I,{"ICG","Local"},'All Apps'!$K:$K,{"Retire","Migrate"}))

Narayan
 
Hi Ian ,

When I try it , I get different results. You should check your data by putting custom filters to see what the correct result is supposed to be.

Narayan
 
Hi Narayan,
I've managed to solve it as follows:

=SUM(SUMIFS('All Apps'!$S:$S,'All Apps'!$C:$C,E1,'All Apps'!$I:$I,{"ICG";"Local"},'All Apps'!$K:$K,{"Retire","Migrate"}))

I simply replaced the comma with a semi colon and it worked. Although infuriatingly, if I change the second criteria to a comma it doesn't again. I'm getting fed up of this. Realistically, it works at the moment, so I'm going with it.

Thank you so much for all your help.

Cheers

Ian.
 
You must log in or register to reply here.
Back
Top