SOLVED

SUMIFS where does not equal items from a range

Copper Contributor

SUMIFS where does not equal items from a range

Hi, I have a sumifs with a few criteria, but want to add a criteria where column x does not equal one of certain names from sheet2 cells d2:d10, any ideas?

Cheers

3 Replies

Re: SUMIFS where does not equal items from a range

=SUMIFS(\$E\$3:\$E\$14,\$C\$3:\$C\$14,D18,\$F\$3:\$F\$14,E18,\$X\$3:\$X\$14,"<>"&D2,\$X\$3:\$X\$14,"<>"&D3,\$X\$3:\$X\$14,"<>"&D4,\$X\$3:\$X\$14,"<>"&D5)

You can set up a SUMIFS formula where you exclude the entries in cells D2:D10 one after another. In the above formula cells D2:D5 are excluded for demonstration.

Re: SUMIFS where does not equal items from a range

I am aware I can do that but I was hoping there would be a formula that could address this, as in reality I will need to add more exclusions to the list and lets say its D2-D100 I don't want to add 98 entries to the formula
best response confirmed by JamesPhImp (Copper Contributor)
Solution

Re: SUMIFS where does not equal items from a range

=SUMPRODUCT((\$C\$3:\$C\$14=\$E\$18)*(\$F\$3:\$F\$14=\$F\$18)*ISNA(MATCH(\$X\$3:\$X\$14,\$D\$2:\$D\$5,0))*\$E\$3:\$E\$14)

You can try SUMPRODUCT. The criteria range is currently D2:D5 and can be extended as required.