SOLVED

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?

Cheers

 

3 Replies

@JamesPhImp 

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

@JamesPhImp 

=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.