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 Nov 23, 2019 at 14:06

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 :) Nov 29, 2019 at 8:52