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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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