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

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

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

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

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