Countif +CountIF Alternative

**GabeGabe** · 11-16-2016, 08:15 PM

I have the following Count if Function to add up the two different variations in ColC
=COUNTIFS(C:C,"<>Gabriel Wong",D:D,"Gabriel Wong",E:E,"<>Gabriel Wong",P:P,T2)+COUNTIFS(C:C,"<>Carson Love",D:D,"Carson Love",E:E,"<>Carson Love",P:P,T2)

I have tried
=Sum(COUNTIFS(C:C,"<>"&{"Carson Love","Gabriel Wong"},D:D,{"Carson Love","Gabriel Wong"},E:E,"<>"{"Carson Love","Gabriel Wong"}",P:P,T3))

And dabbled a bit into Sum Product.

Any way to shorten the Countif + Countif Functions. I add 17 more names and I would rather not have that many countif functions.

I am also constrained to use Google SHeets

**FlameRetired** · 11-17-2016, 12:01 AM

I am not familiar with Google Sheets. This works in desktop Excel.

Put all the names in a lookup column.

Then create a dynamic named range in Name Manager. They shrink or grow as you remove or add data.

If you are not familiar with how to build these:

With the list of names in column M starting at M1 type this formula is some out of the way place and copy it from the formula bar.

=$M$1:INDEX(M:M,MATCH("zzzz",M:M,1)

In Name Manager click "New".
In the Name text box give your formula a name ... NameList might be one.
Then in "Refers to:" paste the formula.
Hit OK.

Then replace the array constants in your formula with NameList. As you add names to the list in column M they will automatically be included in NameList.

**GabeGabe** · 11-17-2016, 09:34 PM

If I am doing this correctly, I have a Name Manger tab under DMA and my formula is now
=ArrayFormula(SUM(COUNTIFS(C:C,"<>"&DMA!A:A,D:D,DMA!A:A,E:E,"<>"&DMA!A:A,P:P,R4)))
I don't get the same answer as when I did the additive countif functions. I tested it out with a simple =ArrayFormula(SUM(COUNTIFS(C:C,DMA!A:A))) and that worked but not sure what I and doing wrong with the Array formula now.

**Jonmo1** · 11-17-2016, 09:57 PM

You can't do the Sum(countifs(.... {criteria1,criteria2})) syntax with <> type of criteria.
Well, you CAN. But it's not doing what you think it is.

That syntax works like an OR, and only works with DOES EQUAL criteria.
Because it's an OR.

So your attempt is saying count if C <> Carson Love OR C <> Gabriel Wong.
No matter what value is in C, that will ALWAYS be TRUE.
if C happens to be Carson Love, then <> Gabriel Wong is TRUE, So the OR is TRUE, and it's counted
If C happens to be Gabriel Wong, then <> Carson Love is TRUE, So the OR is TRUE, and it's counted

**GabeGabe** · 11-18-2016, 12:47 AM

What can I do to get around this?

**XOR LX** · 11-18-2016, 04:08 AM

I see nothing wrong with your attempt at abbreviation, which can also be more efficiently written:

=SUMPRODUCT(COUNTIFS(C:C,"<>"&G1:G2,D:D,G1:G2,E:E,"<>"&G1:G2,P:P,T2))

with your names in G1:G2 (and which can of course be extended).

Regards