Count cell with a certain criteria if adjacent cell is a unique value

[sublimemovement]

I am trying to count cell with a certain criteria if adjacent cell is a unique value.

Example:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Age (Days)[/TD]
[TD]Purchase Order[/TD]
[/TR]
[TR]
[TD]90[/TD]
[TD]20151237[/TD]
[/TR]
[TR]
[TD]27[/TD]
[TD]20151236[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]20151235[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]20151235[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]20151234[/TD]
[/TR]
</tbody>[/TABLE]

I only need to count 1 value from the age column if the Purchase Order column is unique.

The desired result:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Less than 14 days[/TD]
[TD]14 to 30 days[/TD]
[TD]greater than 30[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]


I can manage to get the unique number for the Purchase order column and count my ranges for the age column but I cant mix the 2.

Thanks in advance for all help.

 

[Eric W]

Here's one way. Each purchase order will be counted 1 time, no matter how many times it's in the list. If it's in 2 data ranges, it will still only be included in one range.

Excel 2010

	A	B
Under 14 days
14-30 days
Over 30 days

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA"]Age (Days)[/TD]
[TD="bgcolor: #FAFAFA"]Purchase Order[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]90[/TD]
[TD="bgcolor: #FAFAFA, align: right"]20151237[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"]27[/TD]
[TD="bgcolor: #FAFAFA, align: right"]20151236[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA, align: right"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"]20151235[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA, align: right"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"]20151235[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0[/TD]
[TD="bgcolor: #FAFAFA, align: right"]20151234[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]1[/TD]

</tbody>

Sheet20​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B9:B11[/TH]
[TD="align: left"]{=FREQUENCY(IF(MATCH(B2:B6,B2:B6,0)=ROW(B2:B6)-1,A2:A6),{14,30})}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



This is a somewhat unusual use of an array formula. Select the cells B9:B11 (all 3 of them at the same time), then enter the formula and confirm it with Control-Shift-Enter. Note that to use this formula the result cells must be vertical.

Let me know if this works.

 

[sublimemovement]

Eric,
I get a return of "#N/A" The only thing I change is the range and I take out the curly brackets before entering with "Ctrl+Shift+Enter"

Using my example in OP I get the individual results using the following formulas.

Under 14 Days: =COUNTIF(A:A, "<13")
14-30 Days: =COUNTIFS(A:A, ">13",A:A, "<31")
Over 30 Days: =COUNTIF(A:A, ">30")

Count Unique values: =SUMPRODUCT((B:B<>"")/COUNTIF(B:B,B:B&""))-1

I am trying to count unique values the count what values meet the criteria for the 3 ranges.

 

[Aladin Akyurek]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][/tr]
[tr][td]

1​

[/td][td]Age (Days)[/td][td]Purchase Order[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

2​

[/td][td]

90​

[/td][td]

20151237​

[/td][td][/td][td]

-9.99E+307​

[/td][td]

14​

[/td][td]

30​

[/td][/tr]


[tr][td]

3​

[/td][td]

27​

[/td][td]

20151236​

[/td][td][/td][td]

14​

[/td][td]

30​

[/td][td]

9.99E+307​

[/td][/tr]


[tr][td]

4​

[/td][td]

3​

[/td][td]

20151235​

[/td][td][/td][td]

2​

[/td][td]

1​

[/td][td]

1​

[/td][/tr]


[tr][td]

5​

[/td][td]

3​

[/td][td]

20151235​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

6​

[/td][td]

0​

[/td][td]

20151234​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

In D4 control+shift+enter, not just enter, and copy across:

=SUM(IF(FREQUENCY(IF($A$2:$A$6 >= D2, IF($A$2:$A$6 < D3,$B$2:$B$6)), $B$2:$B$6),1))<strike></strike>

 

[sublimemovement]

Worked perfectly!!! Thanks Aladin!!!

 

[Aladin Akyurek]

Worked perfectly!!! Thanks Aladin!!!

You are welcome.