Counting Unique Items with Multiple Criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

First thank you for your help.
I'm trying to count unique values based on criteria in another column.
What I'm trying to do is count vendors when they have a value associated
with them in another column. An example is:
A2 TRIUMPH B2 2
A3 TRIUMPH B3 3
A4 TRIUMPH B4 3
A5 ABC B5 2
A6 ABC B6 5
A7 DEF B7 blank (nothing entered)
result should be 2 because I don't want to count the blank cells.
Is there any way this can be done?
Thank you for your help
Joe
 
Try the following formula, which needs to be confirmed with
CONTROL+SHIFT+ENTER...

=SUM(IF(FREQUENCY(IF($B$2:$B$7<>"",IF($A$2:$A$7<>"",MATCH($A$2:$A$7,$A$2:
$A$7,0))),ROW($A$2:$A$7)-ROW($A$2)+1),1))

Hope this helps!
 
Note that if you have the following data...
A2 TRIUMPH B2 2
A3 TRIUMPH B3 blank
A4 TRIUMPH B4 3
A5 ABC B5 2
A6 ABC B6 5
A7 DEF B7 blank
Click to expand...

COUNTIF formula -----> 1.666666667

FREQUENCY formula -----> 2

Hope this helps!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Count unique with multiple criteria 3
sort and sum data range with some blank cells 1
Count Unique Values 4
Find the number of the lowest filled row 3
If...rows and columns 3
COUNT function 2
Count Unique Values 4
Alpha sorting (with a twist...?) 3

Back
Top