Count unique

[Spencer Hutton]

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

 

[Luke M]

One way
{=SUM((B2:B1000="Countertops")*(C2:C1000="Granite")/IF(COUNTIF(A2:A1000,A2:A1000)=0,0.1,COUNTIF(A2:A1000,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.

 

[T. Valko]

That returns an incorrect result.

Try this array formula** :

=COUNT(1/FREQUENCY(IF((B2:B1000="countertops")*(C2:C1000="granite"),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.

 

[Spencer Hutton]

that returned 2.6667 not 5. does it matter that there are blank rows included?

 

[Shane Devenshire]

Hi,

It works for me with blanks in columns A, B or C.