Sumifs excluding multiple names

[cartica]

Hi,

I am trying to do a sumifs, and exclude 3 names. When I just exclude 1 name it works. Not sure how to adjust my formula.

=SUM(SUMIFS($V$2:$V$1332,$K$2:$K$1332,"<>eem",$K$2:$K$1332,"<>MERRI12Y LX","<>MERRI1HT LX))

Thanks

[Jonmo1]

You don't need the SUM part, and you left out the criteria range for the 3rd criteria, and left off a quote mark at the very end...

Try
=SUMIFS($V$2:$V$1332,$K$2:$K$1332,"<>eem",$K$2:$K$1332,"<>MERRI12Y LX",$K$2:$K$1332,"<>MERRI1HT LX")

[AliGW]

Have you tried it without SUM(...)? You only need the SUMIFS formula, don't you?

[AlKey]

or this

=SUM(SUMIFS($V$2:$V$1332,$K$2:$K$1332,{"<>eem","<>MERRI12Y LX","<>MERRI1HT LX"}))

[Jonmo1]

@AlKey
That syntax only works for multiple = critera
Won't work for multiple <> criteria
Because it works like an OR.

[AlKey]

@AlKey
That syntax only works for multiple = critera
Won't work for multiple <> criteria
Because it works like an OR.

You are right Jonmo1

[Glenn Kennedy]

=SUM(SUMIFS($V$2:$V$1332,$K$2:$K$1332,"<>eem",$K$2:$K$1332,"<>MERRI12Y LX",$K$2:$K$1332,"<>MERRI1HT LX"))

should do it...

Duhhh. I forgot to refresh before posting. You already have a answer!!

[Ron Coderre]

Try something like this formula that sums $V$2:$V$1332 and subtracts all $V$2:$V$1332 where the Col_K value equals one of the list items:

=SUM($V$2:$V$1332)-SUMPRODUCT(SUMIF($K$2:$K$1332,{"eem","MERRI12Y LX","MERRI1HT LX"},$V$2:$V$1332))

Is that something you can work with?

[cartica]

Thank you all

[Jonmo1]

You're welcome.

I like Ron's logical solution..

[simonator]

Hi all,

What about if I have around +200 criterias and I would like to exclude 80 of them?
Does anyone has a logic for this case?

Thanks

[AliGW]

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread