Forum Discussion
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
=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.
3 Replies
=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.
- 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
=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.
