Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Unique Items with Multiple Criteria
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Unique Items with Multiple Criteria
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! In article , Joe Gieder wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Unique Items with Multiple Criteria
Thank you all for your help these ideas worked perfectly for what I need.
Joe "Joe Gieder" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Unique Items with Multiple Criteria
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 COUNTIF formula ----- 1.666666667 FREQUENCY formula ----- 2 Hope this helps! In article , Joe Gieder wrote: Thank you all for your help these ideas worked perfectly for what I need. Joe "Joe Gieder" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting Unique Items with Multiple Criteria | Excel Worksheet Functions | |||
Counting Unique Items with Multiple Criteria | Excel Worksheet Functions | |||
Counting unique items...please help | Excel Worksheet Functions | |||
counting unique items(values or text) | Excel Worksheet Functions | |||
counting unique items | Excel Discussion (Misc queries) |