Count unique values with countif

See post # 11 by Tony Valko in this thread:http://www.excelforum.com/excel-form...mula-work.html
This should do the trick:

Formula:

Please Login or Register  to view this content.

You'll to edit the cell refrences of course.

Oops!
Thread is http://www.excelforum.com/excel-form...mula-work.html

It's handling wild cards.

populate a unique list based on a count if of another column

That sounds like more than the thread title is saying.
But for the countif you'd only need to change <>"" into <>"x" or any other value in the range which you want to exclude from the count.
So you'd get something like this

Formula:

Please Login or Register  to view this content.

And change A1:A5 to the range you're looking at.
Just upload your workbook if you want me to look at it.

I think this will do the "green one" (for Sheet2!E5).
Assumed that with "based on column A" you're refering to Sheet2!A4 (contains "Alice").

Formula:

Please Login or Register  to view this content.

Looking into the "yellow one" yet.

And for the "yellow one" (for Sheet2!F2):
Needed to add an extra condition to exclude empty cells as you going through the whole column (which I would not recommend).

Formula:

Please Login or Register  to view this content.

Need to count solely for "Alice" in Sheet2!F5? Then just add another condition for that.

Not sure what I was thinking (if I where at all) on the green one. Must have something to do with the color
Should have sticked to the original suggestion.
Next try:

Formula:

Please Login or Register  to view this content.

Finish with [Ctrl]+[Enter]

@FlameRetired

I suspect that works here only because all the names are "Alice" - that approach can only work when the condition can't include and exclude the identical values in the "count unique" column

Originally Posted by daddylonglegs

@FlameRetired

I suspect that works here only because all the names are "Alice" - that approach can only work when the condition can't include and exclude the identical values in the "count unique" column

Actually there is a filter applied in column A of sheet Hole Section Data - March. There is one Casper with a unique corresponding Casing Size of 7 .....or have I missed the point?

Originally Posted by FlameRetired

Actually there is a filter applied in column A of sheet Hole Section Data - March. There is one Casper with a unique corresponding Casing Size of 7

Apologies, I missed that - but my point still stands - your suggested formula works because Casper has a Casing Size which Alice doesn't have. If you change the Casper entry to have a Casing Size the same as one of Alice's entries you'll probably get a result which isn't a whole number

OK, sorry I should have been more specific - if I change the Casper entry in P12 to be the same as the row above, 5.5, then the formula now returns 2.9375, which obviously isn't a valid answer when counting different values

Forget post #11. I was drunk then.
Post #14 should do the trick.

Originally Posted by FlameRetired

I get that same result using one of Tsjallie's formulas (post 11). Mine is in post 15. Could we be talking about different formulas?

daddylonglegs is correct. The solution in post #15 is flawed and only works due to a rather large coincidence.

Regards

Originally Posted by XOR LX

daddylonglegs is correct. The solution in post #15 is flawed and only works due to a rather large coincidence.

Regards

I found my problem and it's not a coincidence.

When I copied and pasted my formula into a different cell I too got 2.9375. The original cell persisted in returning 3 or 4 as the case may be. It turns out that the original cell formatting was set to Fraction "Up to 1/4" !!!!?!!!!!

Also when I Fx'd each formula they also returned 3 and 2.9375 respectively. I don't know what that's all about.

Thank you daddylonglegs and XOR LX for persisting on this one. I would have never caught this one.

Originally Posted by FlameRetired

It turns out that the original cell formatting was set to Fraction "Up to 1/4"

Ouch! That's a tough one to spot!

Originally Posted by FlameRetired

OK. Here's my re-worked formula. I checked formatting and everything. It seems to work.

Formula:

Please Login or Register  to view this content.

Yes. That will do it!

Not to be too negative, but I used to use similar constructions myself in such cases. Unfortunately, one of the things about them is that they tend to be quite resource-heavy.

You wouldn't think it, with just a COUNTIFS inside a SUMPRODUCT, but trust me, the larger the ranges become, the more this set-up struggles.

Whereas the FREQUENCY/MATCH construction, which if anything you would think would be the less efficient of the two (and especially since it requires the use of an exact match_type parameter), is actually surprisingly efficient over larger ranges.

Still, nice alternative and plus one for correctly re-working a difficult construction.

Cheers

Originally Posted by XOR LX

You wouldn't think it, with just a COUNTIFS inside a SUMPRODUCT, but trust me, the larger the ranges become, the more this set-up struggles.

Agreed,

That's why Aladin always (now) suggests the FREQUENCY version - even when it's only a "countdiff" without a condition.

I did a speed test with these two formulas

=SUM(IF(FREQUENCY(IF(D$3:D$1000=D3,IF(B$3:B$1000<>"",MATCH(B$3:B$1000,B$3:B$1000,0))),ROW(B$3:B$1000)-ROW(B$3)+1),1))

=SUMPRODUCT((D$3:D$1000=D3)*(B$3:B$1000<>"")/COUNTIFS(D$3:D$1000,D$3:D$1000,B$3:B$1000,B$3:B$1000&""))

Running several hundred iterations of each the first one is about 7 times faster

I love this thread.
Nice puzzle. Much more fun than Sudoku or Dutch television

Originally Posted by daddylonglegs

Running several hundred iterations of each the first one is about 7 times faster

Yes - that sounds about right from my experiences of the two set-ups, an even greater disparity than I'd imagined, if anything.

Thanks for taking the time to perform the testing.

Cheers

Maybe somewhat off topic, but there is something weird going on in the formula with the frequency.
When it comes at point of evaluating the frequency function which would be likes this:

Formula:

Please Login or Register  to view this content.

it counts the 1's 8 times while there are only 7 1's in the array. The 3's are counted correctly.
This doesn't affect the outcome though.

@Tsjallie

You don't appear to have posted the bins_array as part of your formula, so it's difficult to give you a certain answer.

I take it you're aware that, in general, FREQUENCY returns an array comprising a number of entries which is one greater than that for the bins_array?

Regards

Originally Posted by Tsjallie

it counts the 1's 8 times while there are only 7 1's in the array.

It's not counting eight 1s - it puts 8 in the 1 bin because there are 8 values <= 1 - 7x1 and 1x0

Originally Posted by Tsjallie

I love this thread.
Nice puzzle. Much more fun than Sudoku or Dutch television

Agreed re Sudoku, though unfortunately can't comment on the Dutch television comparison!

@Daddylonglegs, XOR LX
Thanks for clarifying on the frequency. Or rather, waking me up on it :blush: