# Count unique values if 2 other criteria are met

1. ## Count unique values if 2 other criteria are met

Hi everyone.

Firstly, apologies....I know there are plenty of threads about this issue online - after many many wasted hours, I simply can not get it working with my data.

What I am after is a formula which counts the number of unique values from column B (Employer name), when 3 different criteria are met (year, type of event and event cancelled <>Y). For example, if IBM appeared twice in the raw data when "Year= C34" and "Type= B34" and "Cancelled <> Y" - 2 would be the result.

I have data in one sheet, and the results in another. I am stuck with cells D34 in the 'Review of events' sheet, I can then use the same formula for all "Employer participation" stats.
Employers.xlsx

Any help would be greatly appreciated.
Many thanks.

2. ## Re: Count unique values if 2 other criteria are met

Originally Posted by sted999
if IBM appeared twice in the raw data when "Year= C34" and "Type= B34" and "Cancelled <> Y" - 2 would be the result.
If you're counting uniques wouldn't the result be 1?

3. ## Re: Count unique values if 2 other criteria are met

I just downloaded your file and I can't make heads or tails out of it.

Your description doesn't match what I see in the file.

4. ## Re: Count unique values if 2 other criteria are met

Yes, correct the result would be 1 - good spot. My mind is frazzled with how long I've been working on this!

This is just sample data so IBM may also hold a different type of event e.g. One-to-one, so this would be added up in cell D35.

5. ## Re: Count unique values if 2 other criteria are met

Sorry I've not explained that very well. I want the TOTAL number of unique values to be placed in D34 if the other three criteria are met.

6. ## Re: Count unique values if 2 other criteria are met

Hi

in D6 and below to be confirmed with control+shift+enter

=SUM(IF(FREQUENCY(IF(Data!\$J\$2:\$J\$38=C6,IF(Data!\$I\$2:\$I\$38=B6,IF(Data!\$H\$2:\$H\$38<>"Y",MATCH(Data!\$B\$2:\$B\$38,Data!\$B\$2:\$B\$38,0)))),ROW(\$A\$2:\$A\$38)-1)>0,1,0))

Please refer to yellow formulas

Hope it helps

7. ## Re: Count unique values if 2 other criteria are met

Hi canapone.

Thanks a lot for that, it's perfect! Works like an absolute dream.

Thank you

8. ## Re: Count unique values if 2 other criteria are met

Hi,

thanks for providing feedback: I was a little lost in translating formulas from Italian settings.

Greetings from Firenze

##### Users Browsing this Thread

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1