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
|