SUMIFS, exclude range of criteria

**ABSTRAKTUS** · 02-06-2020, 07:18 AM

Hi XL Maestros,

Here I have a SUMIFS where I would like to exclude a dynamic range of values in a column (number of values are changing constantly). I cannot find a similar solution on web, I wonder if anybody has got a workaround or something? The syntax I'm after is something like this:

Please Login or Register  to view this content.

Any help will be much appreciated!

Edited - I suppose the solution, if there will be one, will apply to COUNTIFS too?

Thanks

**Fluff13** · 02-06-2020, 10:44 AM

How about
=SUMIFS($B$2:$B$100,$C$2:$C$100,$C$1,$D$2:$D$100,"<>"&$E$2:$E$20)

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.