COUNTIFS formula

[johns99]

Hello,

I'm having issues with a COUNTIFS formula...I'm using the below

=COUNTIFS($CT$6:$CT$7666,"P1",$X$6:$X$7666,{"x","+"})

this returns a result of 166, however when I filter on the above criteria "P1" and {"x","+"} I'm showing 217

Any ideas why this would not be working?

Thanks in advance,

John

 

[barry houdini]

You are only seeing the count for "x", you need an added SUM function to get the total for "x" and "+"

=SUM(COUNTIFS($CT$6:$CT$7666,"P1",$X$6:$X$7666,{"x","+"}))

 

[bbott]

It's only counting x's (the first value in the array). You would need to add a third argument so that x and + are separate.

 

[johns99]

Thank you both for your quick responses!

@barry houdini I've added the SUM and this fixed the problem. Thank you both, have a great weekend!

 

[johns99]

I do have an additional question that I hope one of you will be able to help me with...

Using the info above...so taking a count of "P1" and {"x","+"}...I am also trying to count information in column W. The issue is there are duplicates in column W that I would not want to include so I can find out how many unique values are in this column.

So I would like to take a count of how many unique values are in column W for people who are labeled with {"x","+"} in column X and "P1" in column CT.

Is this possible?

Let me know if I'm not clear.

 

[barry houdini]

That's a bit trickier but you can use an "array formula" like this

=SUM(IF(FREQUENCY(IF($CT$6:$CT$7666="P1",IF(($X$6:$X$7666="x")+($X$6:$X$7666="+"),IF($W$6:$W$7666<>"",MATCH($W$6:$W$7666,$W$6:$W$7666,0)))),ROW($W$6:$W$7666)-ROW($W$6)+1),1))

confirmed with CTRL+SHIFT+ENTER

 

[johns99]

This worked! Thank you for all your help today, it's much appreciated.

Have a great weekend.

Cheers,

John