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

1. ## 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!!!

Excel Screenshot.JPG

2. ## Re: Count number of unique text values based on 3 conditions?? STUCK!!!

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...

3. ## Re: Count number of unique text values based on 3 conditions?? STUCK!!!

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.

4. ## Re: Count number of unique text values based on 3 conditions?? STUCK!!!

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

5. ## Re: Count number of unique text values based on 3 conditions?? STUCK!!!

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!!

6. ## Re: Count number of unique text values based on 3 conditions?? STUCK!!!

You're welcome.

Here is an explanation of how that formula works.

There are currently 1 users browsing this thread. (0 members and 1 guests)