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