Count number of unique text values based on 3 conditions?? STUCK!!!

Hi all!

Looking for some help with an excel formula that is driving me crazy!!!

Basically, I am trying to use a formula to detect the number of different 'Names' that appear in my table, but by having set criteria in 3 other columns.

For instance, I want the results to only show those that also meet the criteria of Status = "Active", Family = "Williams" and Product = "Slab".

I know that the result should be 2, (as both Marcus and Sirius match this), but I can't work it out!!!

Anybody got any ideas???? So confused by this!!!

Thanks in advance!!!

Excel Screenshot.JPG

Hello and welcome to the forum.

For instance, I want the results to only show those that also meet the criteria of Status = "Active", Family = "Williams" and Product = "Slab".

I am assuming that you mean only count those not only show those. Try this:

=COUNTIFS(A:A,"Active",B:B,"Williams",D:D,"Slab")

Edit: Just realized that you are looking to count the number of unique names based on that criteria. 1 minute...

Hi 63falcondude!

Thanks for the welcome!

I'm trying to count the number of different 'Names' that meet that criteria (Active, Williams, Slab). Not count all of them. Does that make sense?

Your formula gives me the result of 4, as its counting the duplicate 'Sirius' entries, and I'd like to count them only once It should just say 2.

Try this:

=SUM(--(FREQUENCY(IF((A2:A29="Active")*(B2:B29="Williams")*(D2:D29="Slab"),MATCH(C2:C29,C2:C29,0)),ROW(C2:C29)-ROW(C2)+1)>0)) Ctrl Shift Enter

IT WORKS!!! Thanks so much!! I don't understand what on earth the ROW bit at the end means lol, but at least I can analyse my data now!!! Thanks!!

You're welcome.

Here is an explanation of how that formula works.