 # sumifs work in one cell and shows 0 in other

• Hello Guys

Can someone pls explain why the formula works in k1 but not in k2, if anyone has the answer or a different formula pls lemme know

Regards

sobi

## Files

• TEST.xlsx

• Hello,

Can you explain what exactly you want to calculate ...???

Do not forget the difference between using the AND logic ... versus using the OR logic ...

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner • Hello

i basically want to sum A,G, and D S from column A, my sum range is in column G, sorry my formulas are wong in the test file. but i still getting the same issue

Code
1. =SUMIFS(G:G,A:A,"A",A:A,"G",A:A,"D S")

i did use the below formula in cell k1 and this works, but the above formula does not. i will reupload the file again with the correction in formulas. I just wanna know where i am going wrong because if above formula is wrong then the below one shouldn't work as well

Code
1. =SUMIFS(G:G,A:A,"<>A",A:A,"<>G",A:A,"<>D S")

## Files

• TEST.xlsx

• This formula:

=SUMIFS(G:G,A:A,"A",A:A,"G",A:A,"D S")

is trying to sum column G where column A is "A" and it's "G" and it's "D S" all on the same row. Since column A can't be all three values at once, you get 0. You need an OR condition as Carim suggested, so you need an array of criteria and then to sum the results, like this:

=SUM(SUMIFS(G:G,A:A,{"A","G","D S"}))

Rory
Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

• thank you for the reply, just for uderstanding, why the formula works if i use<> , the only difference between the two formulas is that <> is missing

• thank you for the reply, just for understanding, why the formula works if i use<> , the only difference between the two formulas is that <> is missing

As explained by Rory, all 3 criteria are located in Column A ... and, as stated in message # 2, in my opinion ...

you should take the time to fully master the difference between using the AND logic ... versus using the OR logic ...

You have not explained the exact calculation you want to get ... most probably you need the OR logic ... If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner • The <> logic works because you want each row to not be "A" and to not be "G" and to not be "D S" - i.e. to not be any of the three.

Rory
Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

• Thanks for your Thanks ... AND for the Likes If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner 