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:

Age (Days)	Purchase Order
90	20151237
27	20151236
3	20151235
3	20151235
0	20151234

<tbody>
</tbody>

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

The desired result:

Less than 14 days	14 to 30 days	greater than 30
2	1	1

<tbody>
</tbody>

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
1	Age (Days)	Purchase Order
2	90	20151237
3	27	20151236
4	3	20151235
5	3	20151235
6	0	20151234
7
8
9	Under 14 days	2
10	14-30 days	1
11	Over 30 days	1

<tbody>
</tbody>

Sheet20​

Array Formulas Cell Formula B9:B11 {=FREQUENCY(IF(MATCH(B2:B6,B2:B6,0)=ROW(B2:B6)-1,A2:A6),{14,30})} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

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]

Row\Col	A​	B​	C​	D​	E​	F​
1​	Age (Days)	Purchase Order
2​	90​	20151237​		-9.99E+307​	14​	30​
3​	27​	20151236​		14​	30​	9.99E+307​
4​	3​	20151235​		2​	1​	1​
5​	3​	20151235​
6​	0​	20151234​

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.