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

 

[Aladin Akyurek]

Care to post 4 records from J and Q as it's not clear what range must be counted?

 

[Jed Shields]

Care to post 4 records from J and Q as it's not clear what range must be counted?

I'll try and make it clearer, and to simplify it I'll go with columns A and B:

<colgroup><col style="mso-width-source:userset;mso-width-alt:3474;width:71pt" width="95"> <col style="width:48pt" width="64"> </colgroup><tbody>
[TD="class: xl63, width: 95"]In Process[/TD]
[TD="class: xl64, width: 64"]PR1[/TD]

[TD="class: xl63"]In Process[/TD]
[TD="class: xl64"]PR1[/TD]

[TD="class: xl63"]On Hold[/TD]
[TD="class: xl64"]PR3[/TD]

[TD="class: xl63"]In Process[/TD]
[TD="class: xl64"]PR2[/TD]

[TD="class: xl63"]On Hold[/TD]
[TD="class: xl64"]PR4[/TD]

</tbody>

So in the above table, I want to count all "In Process" records in Col A but ignore duplicate PR numbers in Col B. There are two In Process with PR1, so I only want to count one of them. The other In Process is PR2, so I want to count that as well. So three in total.

 

[Aladin Akyurek]

Thanks for the sample.

Control+shift+enter, not just enter:

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

 

[Jed Shields]

Perfect, many thanks

 

[Aladin Akyurek]

Perfect, many thanks

You are welcome.

 

[Jed Shields]

One more question... I also need to count a subset of this based on a date in column C. For example, where the date is greater than 31/1/18 and less than 30/6/18...

 

[Aladin Akyurek]

One more question... I also need to count a subset of this based on a date in column C. For example, where the date is greater than 31/1/18 and less than 30/6/18...

Control+shift+enter:


=SUM(IF(FREQUENCY(IF($A$2:$A$6="in process",IF($B$2:$B$6<>"",IF($C$2:$C$6 > "31/1/18"+0,IF($C$2:$C$6 < "30/6/18"+0,MATCH($B$2:$B$6,$B$2:$B$6,0))))),ROW($B$2:$B$6)-ROW($B$2)+1),1))

 

[Jed Shields]

Back again!

The above has been working perfectly, modified formula below for a different workbook:

={SUM(IF(FREQUENCY(IF('Raw Data'!$M$2:$M$10000="inprocess",IF('Raw Data'!$W$2:$W$10000<>"-",IF('RawData'!$AT$2:$AT$10000 = $AG2,MATCH('Raw Data'!$X$2:$X$10000,'RawData'!$X$2:$X$10000,0)))),ROW('Raw Data'!$X$2:$X$10000)-ROW('RawData'!$X$2)+1),1))}

Basically, looking for “In Process” in column M, “-“ incolumn W, Department Name in column AT and then counting any unique instances in column X (PO number).

I now need to try and work out the Average Days, which is found in column AU.

Any ideas how I do this? I’m assuming that I’ll need to amend the above formula to find and add the Days for each line in the array and then divide by the original formula. It’s working out how to add the Day part that I’m stuck on…

 

[Aladin Akyurek]

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