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

[TABLE="width: 285"]
<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>[TR]
[TD="width: 102, bgcolor: white"]A[/TD]
[TD="width: 76, bgcolor: white"]B[/TD]
[TD="width: 73, bgcolor: white"]C[/TD]
[TD="width: 64, bgcolor: white"]D[/TD]
[TD="width: 64, bgcolor: white"]E[/TD]
[/TR]
[TR]
[TD="bgcolor: white"]Status[/TD]
[TD="bgcolor: white"]Date[/TD]
[TD="bgcolor: white"]Dept[/TD]
[TD="bgcolor: white"]PO No[/TD]
[TD="bgcolor: white"]Days
[/TD]
[/TR]
[TR]
[TD="width: 102, bgcolor: white"]In Process[/TD]
[TD="bgcolor: white"]01/02/2019[/TD]
[TD="bgcolor: white"]IT[/TD]
[TD="width: 64, bgcolor: #92D050"]PO1[/TD]
[TD="bgcolor: #92D050"]2 Days[/TD]
[/TR]
[TR]
[TD="width: 102, bgcolor: white"]In Process[/TD]
[TD="bgcolor: white"]01/01/2019[/TD]
[TD="bgcolor: white"]Sales[/TD]
[TD="width: 64, bgcolor: #E26B0A"]PO3[/TD]
[TD="bgcolor: #E26B0A"]5 Days[/TD]
[/TR]
[TR]
[TD="width: 102, bgcolor: white"]In Process[/TD]
[TD="bgcolor: white"]31/01/2019[/TD]
[TD="bgcolor: white"]IT[/TD]
[TD="width: 64, bgcolor: #92D050"]PO2[/TD]
[TD="bgcolor: #92D050"]1 Day[/TD]
[/TR]
[TR]
[TD="width: 102, bgcolor: white"]In Process[/TD]
[TD="bgcolor: white"]13/02/2019[/TD]
[TD="bgcolor: white"]IT[/TD]
[TD="width: 64, bgcolor: transparent"]PO2[/TD]
[TD="bgcolor: transparent"]1 Day[/TD]
[/TR]
[TR]
[TD="width: 102, bgcolor: white"]On Hold[/TD]
[TD="bgcolor: white"]10/01/2019[/TD]
[TD="bgcolor: white"]Sales[/TD]
[TD="width: 64, bgcolor: white"]PO4[/TD]
[TD="bgcolor: white"]1 Day[/TD]
[/TR]
</tbody>[/TABLE]



I'm expecting to see:

[TABLE="width: 160"]
<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>[TR]
[TD="width: 76, bgcolor: transparent"] [/TD]
[TD="width: 73, bgcolor: white"]Unique PRs
[/TD]
[TD="width: 64, bgcolor: white"]Ave Days
[/TD]
[/TR]
[TR]
[TD="bgcolor: #92D050"]IT[/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]1.5 Days
[/TD]
[/TR]
[TR]
[TD="bgcolor: #E26B0A"]Sales
[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]5 Days[/TD]
[/TR]
</tbody>[/TABLE]

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.