0

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,
"-")
1
  • Please share a copy of your spreadsheet (excluding private or confidential information
    – Tedinoz
    Nov 23, 2019 at 14:06

1 Answer 1

1

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.

1
  • Thank you, worked a treat. Really helpful :)
    – Laura
    Nov 29, 2019 at 8:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.