SUMIFS where does not equal items from a range

Copper Contributor

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?



3 Replies




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
best response confirmed by JamesPhImp (Copper Contributor)




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