Countif without duplicates?

[Jed Shields]

Hello all,

I'm trying to do a count of records that are "In Process" in column J, but only count once for each PR reference in column Q. For example, there may be 4 "In Process" records, but three have PR1 and one has PR2, so I'd want to count 2 i.e. not count the duplicates.

Cheers,

Jed

 

[Jed Shields]

Just to be safe, could you post a small sample along with desired result?

Okay, to simplify I'll use cols A:E

A	B	C	D	E
Status	Date	Dept	PO No	Days
In Process	01/02/2019	IT	PO1	2 Days
In Process	01/01/2019	Sales	PO3	5 Days
In Process	31/01/2019	IT	PO2	1 Day
In Process	13/02/2019	IT	PO2	1 Day
On Hold	10/01/2019	Sales	PO4	1 Day

<colgroup><col width="102" style="width: 77pt; mso-width-source: userset; mso-width-alt: 3640;"> <col width="76" style="width: 57pt; mso-width-source: userset; mso-width-alt: 2702;"> <col width="73" style="width: 55pt; mso-width-source: userset; mso-width-alt: 2588;"> <col width="64" style="width: 48pt;" span="2"> <tbody> </tbody>

I'm expecting to see:

	Unique PRs	Ave Days
IT	2	1.5 Days
Sales	1	5 Days

<colgroup><col width="76" style="width: 57pt; mso-width-source: userset; mso-width-alt: 2702;"> <col width="73" style="width: 55pt; mso-width-source: userset; mso-width-alt: 2588;"> <col width="64" style="width: 48pt;"> <tbody> </tbody>

So the third IT isn't counted as it is a repeat of PO2.

The second Sales isn't counted as it is On Hold.

All PO numbers that are the same code will have the same number of days

 

[Aladin Akyurek]

Book1
	A	B	C	D	E	F	G	H	I
1	Status	Date	Dept	PO No	Days			Unique PRs	Ave Days
2	In Process	2/1/2019	IT	PO1	2		IT	2	1.5
3	In Process	1/1/2019	Sales	PO3	5		Sales	1	5
4	In Process	1/31/2019	IT	PO2	1
5	In Process	2/13/2019	IT	PO2	1
6	On Hold	1/10/2019	Sales	PO4	1
Sheet1

In H2 control+shift+enter, not just enter, and copy down:

=SUM(IF(FREQUENCY(IF($A$2:$A$6="in process",IF($D$2:$D$6<>"",IF($C$2:$C$6=G2,MATCH($D$2:$D$6,$D$2:$D$6,0)))),ROW($D$2:$D$6)-ROW($D$2)+1),1))

In I2 control+shift+enter, not just enter, and copy down:

=AVERAGE(IF(FREQUENCY(IF($A$2:$A$6="in process",IF($D$2:$D$6<>"",IF($C$2:$C$6=G2,MATCH($D$2:$D$6,$D$2:$D$6,0)))),ROW($D$2:$D$6)-ROW($D$2)+1),$E$2:$E$6))

 

[Jed Shields]

Works perfectly, many thanks :D

 

[Aladin Akyurek]

Works perfectly, many thanks :D

You are welcome.