Google-sheets – Google Sheets SUMIFS issue

google sheetsgoogle-sheets-arrayformulagoogle-sheets-query

I'm trying to do a SUMIFS where one of the criteria has multiple strings in the same column, but the others are just based upon 1 criteria. As you can probably guess I'm having the issue that only the 1st criteria inside my array is being found and the others aren't. Can anyone help me restructure my formula so it works or tell me which is the correct function to use?

=IFERROR(
          SUMIFs($S$4:$S$5006,
                 $E$4:$E$5006,
                 "<"&'$A$1,
                  $G$4:$G$5006,
                  {"*C1*","*C2*","*C3*","*C4*","*C5*"},
                  $B$4:$B$5006,
                  $A$1,
                  !$P$4:$P$5006,
                  "South",
                  $C$4:$C$5006,
                  "=4",
                  $S$4:$S$5006,
                  ">0")
/$M14,
"-")

Best Answer

You need to wrap your sumifs in a sum so like so (due to you using the array trick):

=IFERROR(
          SUM(SUMIFs($S$4:$S$5006,
                 $E$4:$E$5006,
                 "<"&'$A$1,
                  $G$4:$G$5006,
                  {"*C1*","*C2*","*C3*","*C4*","*C5*"},
                  $B$4:$B$5006,
                  $A$1,
                  !$P$4:$P$5006,
                  "South",
                  $C$4:$C$5006,
                  "=4",
                  $S$4:$S$5006,
                  ">0")
/$M14),
"-")

See this link for an explanation

I highly recommend you just use the Query function which is really made for this kind of situation.

Related Topic