Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count unique
I am trying to count the number of unique job numbers that have the category
= countertops and the sub-category=granite. Note: job 20470098 has 2 instances of Granite, i only want to count this once. i am trying to identify the number of jobs that have used this category. This formula {Sum((B2:B1000="Countertops")*(C2:C1000="Granite") )} returns the result 5 since there are 5 instances of Countertops-Granite. i am looking for the result 4 since there are only 4 jobs that have this category-subcategory, one just happens to have it twice and i dont need to count it twice. Thank you. Job Category Sub-Category 20470071 Countertops Granite 20470071 Countertops Marble 20470098 Countertops Granite 20470098 Countertops Granite 20470098 Countertops Marble 20470106 Countertops Granite 20470109 Countertops Granite 20470109 Countertops Marble |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count unique
One way,
{=SUM((B2:B1000="Countertops")*(C2:C1000="Granite" )/IF(COUNTIF(A2:A1000,A2:A1000)=0,0.1,COUNTIF(A2:A10 00,A2:A1000))){ Just as an FYI, the 0.1 in the IF statement is simply to avoid XL thinking there's an Div/0 error, when in reality the math operation is 0/0. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Spencer Hutton" wrote: I am trying to count the number of unique job numbers that have the category = countertops and the sub-category=granite. Note: job 20470098 has 2 instances of Granite, i only want to count this once. i am trying to identify the number of jobs that have used this category. This formula {Sum((B2:B1000="Countertops")*(C2:C1000="Granite") )} returns the result 5 since there are 5 instances of Countertops-Granite. i am looking for the result 4 since there are only 4 jobs that have this category-subcategory, one just happens to have it twice and i dont need to count it twice. Thank you. Job Category Sub-Category 20470071 Countertops Granite 20470071 Countertops Marble 20470098 Countertops Granite 20470098 Countertops Granite 20470098 Countertops Marble 20470106 Countertops Granite 20470109 Countertops Granite 20470109 Countertops Marble |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count unique
That returns an incorrect result.
Try this array formula** : =COUNT(1/FREQUENCY(IF((B2:B1000="countertops")*(C2:C1000="g ranite"),A2:A1000),A2:A1000)) Better to use cells to hold the criteria: E2 = Countertops F2 = Granite =COUNT(1/FREQUENCY(IF((B2:B1000=E2)*(C2:C1000=F2),A2:A1000) ,A2:A1000)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Luke M" wrote in message ... One way, {=SUM((B2:B1000="Countertops")*(C2:C1000="Granite" )/IF(COUNTIF(A2:A1000,A2:A1000)=0,0.1,COUNTIF(A2:A10 00,A2:A1000))){ Just as an FYI, the 0.1 in the IF statement is simply to avoid XL thinking there's an Div/0 error, when in reality the math operation is 0/0. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Spencer Hutton" wrote: I am trying to count the number of unique job numbers that have the category = countertops and the sub-category=granite. Note: job 20470098 has 2 instances of Granite, i only want to count this once. i am trying to identify the number of jobs that have used this category. This formula {Sum((B2:B1000="Countertops")*(C2:C1000="Granite") )} returns the result 5 since there are 5 instances of Countertops-Granite. i am looking for the result 4 since there are only 4 jobs that have this category-subcategory, one just happens to have it twice and i dont need to count it twice. Thank you. Job Category Sub-Category 20470071 Countertops Granite 20470071 Countertops Marble 20470098 Countertops Granite 20470098 Countertops Granite 20470098 Countertops Marble 20470106 Countertops Granite 20470109 Countertops Granite 20470109 Countertops Marble |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count unique
that returned 2.6667 not 5. does it matter that there are blank rows included?
"Luke M" wrote: One way, {=SUM((B2:B1000="Countertops")*(C2:C1000="Granite" )/IF(COUNTIF(A2:A1000,A2:A1000)=0,0.1,COUNTIF(A2:A10 00,A2:A1000))){ Just as an FYI, the 0.1 in the IF statement is simply to avoid XL thinking there's an Div/0 error, when in reality the math operation is 0/0. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Spencer Hutton" wrote: I am trying to count the number of unique job numbers that have the category = countertops and the sub-category=granite. Note: job 20470098 has 2 instances of Granite, i only want to count this once. i am trying to identify the number of jobs that have used this category. This formula {Sum((B2:B1000="Countertops")*(C2:C1000="Granite") )} returns the result 5 since there are 5 instances of Countertops-Granite. i am looking for the result 4 since there are only 4 jobs that have this category-subcategory, one just happens to have it twice and i dont need to count it twice. Thank you. Job Category Sub-Category 20470071 Countertops Granite 20470071 Countertops Marble 20470098 Countertops Granite 20470098 Countertops Granite 20470098 Countertops Marble 20470106 Countertops Granite 20470109 Countertops Granite 20470109 Countertops Marble |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count unique
Hi,
It works for me with blanks in columns A, B or C. -- If this helps, please click the Yes button Cheers, Shane Devenshire "Spencer Hutton" wrote: that returned 2.6667 not 5. does it matter that there are blank rows included? "Luke M" wrote: One way, {=SUM((B2:B1000="Countertops")*(C2:C1000="Granite" )/IF(COUNTIF(A2:A1000,A2:A1000)=0,0.1,COUNTIF(A2:A10 00,A2:A1000))){ Just as an FYI, the 0.1 in the IF statement is simply to avoid XL thinking there's an Div/0 error, when in reality the math operation is 0/0. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Spencer Hutton" wrote: I am trying to count the number of unique job numbers that have the category = countertops and the sub-category=granite. Note: job 20470098 has 2 instances of Granite, i only want to count this once. i am trying to identify the number of jobs that have used this category. This formula {Sum((B2:B1000="Countertops")*(C2:C1000="Granite") )} returns the result 5 since there are 5 instances of Countertops-Granite. i am looking for the result 4 since there are only 4 jobs that have this category-subcategory, one just happens to have it twice and i dont need to count it twice. Thank you. Job Category Sub-Category 20470071 Countertops Granite 20470071 Countertops Marble 20470098 Countertops Granite 20470098 Countertops Granite 20470098 Countertops Marble 20470106 Countertops Granite 20470109 Countertops Granite 20470109 Countertops Marble |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
count unique | Excel Worksheet Functions | |||
count unique | Excel Worksheet Functions | |||
Attempting to sort unique/only count first record in each unique g | Excel Discussion (Misc queries) | |||
Count # of unique occurances | Excel Worksheet Functions | |||
Count Unique Entries | Excel Worksheet Functions |