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.

Your help is appreciated.

pt

Looks like 1 every time to me.

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

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

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

Worked like a charm. BIG formula. Thx Bob

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

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)