Count Unique

[CHC Consulting LLC]

Please help!!!


Formula to go in cell AR2....


I need to look up the contents in AP2 in column AP and count the associated unique job numbers in column S. I would also like to add the condition that column A equals "CCAP". Is this possible?

Hopefully this makes sense... I am cross eyed at this point.

TIA

CHC

 

[barry houdini]

Try this formula if the job numbers are numeric

=SUM(IF(FREQUENCY(IF(AP$2:AP$100=AP2,IF(A$2:A$100="CCAP",S$2:S$100)),S$2:S$100),1))

confirm with CTRL+SHIFT+ENTER

 

[CHC Consulting LLC]

Thank you.

Sample job number: A014BEJ

I entered your formula and it returned mostly #N/A's and some 0's. Any other ideas?

 

[AhoyNC]

Try changing Barry's formula to (since your job numbers are not numeric).

<colgroup><col width="925"></colgroup><tbody>
</tbody>

[TABLE="width: 925"]
<colgroup><col width="925"></colgroup><tbody>[TR]
   [TD="width: 925"]=SUM(IF(FREQUENCY(IF(AP$2:AP$100=AP2,IF(A$2:A$100="CCAP",MATCH($S$2:$S$100,$S$2:$S$100,0))),ROW($S$2:$S$100)-ROW($S$2)+1),1))[/TD]
 [/TR]
</tbody>[/TABLE]

 

[CHC Consulting LLC]

Thank you... Still calculating

 

[Aladin Akyurek]

Thank you... Still calculating

What does that still calculating mean?

 

[CHC Consulting LLC]

What does that still calculating mean?

The formula took a very long time to calculate.

 

[CHC Consulting LLC]

Try changing Barry's formula to (since your job numbers are not numeric).

<tbody>
</tbody>

[TABLE="width: 925"]
<tbody>[TR]
[TD="width: 925"]=SUM(IF(FREQUENCY(IF(AP$2:AP$100=AP2,IF(A$2:A$100="CCAP",MATCH($S$2:$S$100,$S$2:$S$100,0))),ROW($S$2:$S$100)-ROW($S$2)+1),1))[/TD]
[/TR]
</tbody>[/TABLE]

Thank you. Unfortunately something went wrong as I got incorrect counts. For example the formula returned a 10, when their were only 3 unique job numbers. Whats even more strange is that there were only 6 rows that matched the column AP condition?

 

[barry houdini]

Did you use CTRL+SHIFT+ENTER?

 

[Aladin Akyurek]

The formula took a very long time to calculate.

Care to post the formula as you implemented it?