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