Help with SUMIFS

[chrono2483]

Hello,

I am working with a SUMIFS formula, where I want to target specific words in Col G (total of 40+ different words), and be able to reference their corresponding value in Col C, and add them up to give a total value. However, it always returns a 0. The formatting is in [h]:mm.

I use a similar SUMIF, with different words using the same columns and formatting and it works and returns the total value. I verified that the values in Col C are actually numbers, by using =ISNUMBER().

Here is a condensed version of the formula, using only three specific words, yet it still returns 0.

Formula: =SUMIFS(Data!C:C, Data!G:G,"*SNACK*", Data!G:G,"*LUNCH*", Data!G:G,"*PAUSE*")

Any reason why the SUMIF works, but SUMIFS does not?

 

[bschwartz]

The SUMIFS criteria arguments are like AND statements. All need to be true in order for it to add them. If you want to add when the values contain snack OR Lunch OR Pause, you will need to do SUMIF function for SNACK + sumif function for lunch, etc.

 

[chrono2483]

Great, thanks bschwartz - good to know, and works perfectly now!

 

[bschwartz]

No problem. Glad I could help.

 

[Aladin Akyurek]

Hello,

I am working with a SUMIFS formula, where I want to target specific words in Col G (total of 40+ different words), and be able to reference their corresponding value in Col C, and add them up to give a total value. However, it always returns a 0. The formatting is in [h]:mm.

I use a similar SUMIF, with different words using the same columns and formatting and it works and returns the total value. I verified that the values in Col C are actually numbers, by using =ISNUMBER().

Here is a condensed version of the formula, using only three specific words, yet it still returns 0.

Formula: =SUMIFS(Data!C:C, Data!G:G,"*SNACK*", Data!G:G,"*LUNCH*", Data!G:G,"*PAUSE*")

Any reason why the SUMIF works, but SUMIFS does not?

Try rather:

=SUM(SUMIFS(Data!C:C, Data!G:G,{"*SNACK*","*LUNCH*","*PAUSE*"}))