Sumifs works under certain conditions

[howard]

I have the following sumifs formula which gives me the correct result

 =SUM(SUMIFS(C:C,B:B,({"*lab mon*","*lab fast*"})))

However if I use the following formula , it returns zero

 =SUMIFS(C:C,B:B,"*lab mon*",B:B,"*lab fast*")

It would be appreciated if someone can assist me

 

[Peter_SSs]

The second formula simply has the wrong syntax for what you are trying to achieve. That formula is looking for rows that have both "*lab mon*" and "*lab fast*" on the same row in column B, whereas you appear to be looking for "*lab mon*" or "*lab fast*" in column B.

It would be appreciated if someone can assist me

Since you already have a formula that returns the correct value, in what way do you need help?

 

[howard]

Hi Peter

I was trying to find out why the second formula was not working i.e returning zero

 

[Peter_SSs]

Hi Peter

I was trying to find out why the second formula was not working i.e returning zero

OK, so did the first part of my answer clarify that for you?

 

[howard]

Thanks it did


I am try to add values in Col C where "*lab mon*" and "*lab fast*" appears in Col B

 

[Peter_SSs]

I am try to add values in Col C where "*lab mon*" and "*lab fast*" appears in Col B

If the first formula works, then, no, you are trying to add values in Col C where "*lab mon*" or "*lab fast*" appears in Col B

 

[howard]

Thanks, How to I amend the second formula to give the same result ?

 

[Peter_SSs]

Thanks, How to I amend the second formula to give the same result ?

This brings me back to my earlier question. Why bother when you (apparently) have a perfectly good formula producing the result you want?

However:

=SUMIF(B:B,"*lab mon*",C:C)+SUMIF(B:B,"*lab fast*",C:C)

Note that this formula, and your original one, will double-count a row if a cell in column B contains both "*lab mon*" and "*lab fast*".

 

[howard]

You are 100% correct. If solution works why bother