Forum Discussion
Solved
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.
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.