Sum cells only if not zero or blank

[samir_aguiar]

Hello there,

I searched everywhere and couldn't find what I'm looking for. I have two columns, say A and B and I want to sum the cells of B. I want only to sum the cells where the cell by its side (A cell) is not zero or blank.

How do I do that? I tried SUMIF but couldn't add another condition...

Thanks in advance.

 

[gardnertoo]

Use SUMPRODUCT instead. Say your range is A2:B20, the formula is:

=SUMPRODUCT(B2:B20,--(A2:A20<>0))

The "double minus" in front of the second term is important, it forces a string of TRUE,FALSE,TRUE,TRUE..... to become ones and zeros.

 

[Haseeb Avarakkan]

If you are looking to SUM, Col_A is >0 or <0, try

=SUM(SUMIF(A:A,{">0","<0"},B:B))

 

[samir_aguiar]

Thanks a lot, both worked! One question, why I get a 0 when I change the < and > condition in the second one? (from {">0","<0"} to {"<0",">0"})

Edit: instead of =SUM(SUMIF(A:A,{">0","<0"},B:B)) I just used SUMIF(A:A,{">0","<0"},B:B). Any differences?

 

[Haseeb Avarakkan]

instead of =SUM(SUMIF(A:A,{">0","<0"},B:B)) I just used SUMIF(A:A,{">0","<0"},B:B). Any differences?

Yes. There is difference. It just SUM the first condition, which is ">0". Will not SUM "<0"

If you just want to SUM >0 numbers, just use

=SUMIF(A:A,">0",B:B)

 

[samir_aguiar]

Got it! Thanks a lot!