# Counting Unique Values on Multiple Criteria

1. ## Counting Unique Values on Multiple Criteria

I've been faced with this dilemna for 3 days now. I have a table of data with duplicate information wherein I need to count each occurence of each duplicate in a single column only once based on 4 different criteria.

Sample file is attached in .xlsx format.

Blue columns represent the criteria and Yellow column represents what I'm counting. I've been using COUNTIFS based on the criteria, but it doesn't count unique values.

pt

2. ## Re: Counting Unique Values on Multiple Criteria

Looks like 1 every time to me.

3. ## Re: Counting Unique Values on Multiple Criteria

Originally Posted by Bob Phillips
Looks like 1 every time to me.
Thank you for the response. However, let me reiterate my dilema. I've attached a more comprehensive file of what I'm up against to help explain the matter.

Please take a look when you have a moment....

4. ## Re: Counting Unique Values on Multiple Criteria

Try this array formula

=SUM(--(FREQUENCY(IF((\$B\$2:\$B\$1000=\$M\$4)*(\$D\$2:\$D\$1000=\$K\$5)*(\$H\$2:\$H\$1000=\$L5),MATCH(\$I\$2:\$I\$1000,\$I\$2:\$I\$1000,0)),ROW(INDIRECT("1:"&ROWS(\$I\$2:\$I\$1000))))>0))

5. ## Re: Counting Unique Values on Multiple Criteria

I'll give it a shot and let you know....Thanks again.

6. ## Re: Counting Unique Values on Multiple Criteria

Worked like a charm. BIG formula. Thx Bob

7. ## Re: Counting Unique Values on Multiple Criteria

I, too, have been trying to get this formula to work for a similar issue with countifs and other tricks for days. This one worked on the first go! Yippeee!!! Awesome. Thank you so much!!!!!!

8. ## Re: Counting Unique Values on Multiple Criteria

Originally Posted by Bob Phillips
Try this array formula

=SUM(--(FREQUENCY(IF((\$B\$2:\$B\$1000=\$M\$4)*(\$D\$2:\$D\$1000=\$K\$5)*(\$H\$2:\$H\$1000=\$L5),MATCH(\$I\$2:\$I\$1000,\$I\$2:\$I\$1000,0)),ROW(INDIRECT("1:"&ROWS(\$I\$2:\$I\$1000))))>0))
Hi Bob
I'd like to ask a question that deals with counting blanks, instead of summing. I have column of data and I am counting the blank cells by using Countblank(A1:A101). Next month I will have more rows of data, say 200 rows and I'd like my formula to count the blanks to the last non-blank value in the column. Is there a way to do that in a formula instead of VB?
Thanks
Joe (new member to list)

9. ## Re: Counting Unique Values on Multiple Criteria

powersj,

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