asked on

formula to count unique values across column A, when correlated column B=X and column C=Y

Derived from this question: http:Q_26923609.html

For which I had an answer. But this seems like a common enough problem, so is there a better, shorter answer?

=SUM(N(FREQUENCY(C2:C25*(V2:V25="sometext")*(U2:U25="othertext"),C2:C25*(V2:V25="sometext")*(U2:U25="othertext"))>0))-(OR(COUNTIF(V2:V25,"*")<>ROWS(V2:V25),COUNTIF(U2:U25,"*")<>ROWS(V2:V25)))

barry houdini

Does that work, Richard?

I saw your reply to the original question and considered posting a suggestion but your formula seemed to do the trick OK.....but I don't think the above gets the correct answer...

I'm assuming you want to look at all the values in C where U and V match the criteria ...and get a count of the different values, excluding blanks.

This would be a fairly standard approach (if expensive, resource-wise)

=SUM(IF(FREQUENCY(IF(U2:U25="sometext",IF(V2:V25="othertext",IF(C2:C25<>"",MATCH(C2:C25,C2:C25,0)))),ROW(C2:C25)-ROW(C2)+1),1))

array-entered

That will work for any type of value in C2:C25, not just numbers

regards, barry

barry houdini

Actually I transposed "sometext" and "othertext"......but still I think your formula works OK when there are blank rows in the range - but when there aren't it overcounts by one

barry

cyberkiwi

ASKER

Does it still overcount with the final answer? I changed countif(.. "") to countif(.. "*") - and one failure of doing any maths (the "*(boolean)" trick is that blanks are turned into 0, which get counted by frequency), hence the optional subtraction by 1 at the end.

Array-entered..
I'll try that a bit later, but is there any possibility of a non-array formula, arrays are expensive. The formula I had does not need CSE

cyberkiwi

ASKER

I meant to increase it to 30 for a well thought out alternative :)

ASKER CERTIFIED SOLUTION

barry houdini

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cyberkiwi

ASKER

It looks good, but how in the world is it doing the count-unique part?
This is looking promising - I think 50 is in order

cyberkiwi

ASKER

I get it now.
And you are right, there are quite a few cases where my formula will cause the result to be out by 1.

Thanks Barry