# Count first instance of duplicate value with multiple criteria

#### Black Arrow

##### New Member
Hi,

Can I count the first instance of a duplicate value with multiple criteria?

Based on below table we have the following:
- 3 unique companies
- 3 unique PO's
- 12 items across 3 PO's

Based on below table I want the following:
- Cell B2: This cell should reflect the amount of PO acknowledgements, based on if there is an "X" or not in the cell of the first item of a PO.
- Cell C2: This cell should reflect the amount of PO acknowledgements overdue, based on if there is an "X" in the cell of the first item of a PO and 2 weeks in the past

Example: Company 1, PO 111, the first item is item 2, since item 1 is cancelled (hypothetically), so if the "X" is removed from cell F4, this should count as 1
Example: Company 2, PO 222, the first item is item 1, so if the "X" is removed from cell F5, this should count is 1 more PO Acknowledgement received. And on top of that, if I would remove the "X" from PO 222, item 2, I do NOT want Excel to count this as an extra PO Acknowledgement received, since item 1 is already unticked.

#### Marcelo Branco

##### MrExcel MVP
Maybe...

B2
=SUMPRODUCT(--(MATCH(Table1[Company],Table1[Company],0)=ROW(Table1[Company])-MIN(ROW(Table1[Company]))+1))
confirmed with just Enter

C2
=SUMPRODUCT(--(MATCH(Table1[Company],Table1[Company],0)=ROW(Table1[Company])-MIN(ROW(Table1[Company]))+1),--(Table1[PO Ack.]="X"))
confirmed with just Enter

M.

### Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

#### Black Arrow

##### New Member
Maybe...

B2
=SUMPRODUCT(--(MATCH(Table1[Company],Table1[Company],0)=ROW(Table1[Company])-MIN(ROW(Table1[Company]))+1))
confirmed with just Enter

C2
=SUMPRODUCT(--(MATCH(Table1[Company],Table1[Company],0)=ROW(Table1[Company])-MIN(ROW(Table1[Company]))+1),--(Table1[PO Ack.]="X"))
confirmed with just Enter

M.
Hi Marcelo Branco,

It looks like the formula in B2 reflects the amount of unique companies?

The formula in C2 is indeed what I need for the amount of PO Acknowledgements, thanks!

Now all I need is something that counts the PO's once they are overdue. For example 14 days ago counting from today (6 July) is 22 June. So what I want to see in D2 is value 2, since PO 111 was issued 6/22 and PO 222 was issued 6/5 and both of them still have the PO Ack. pending. PO 333 should not be included since this was issued 5 days ago, so not overdue yet.

#### Marcelo Branco

##### MrExcel MVP
Yes, the formula in B2 returns the count of unique companies

I didn't fully understand what you want in D2.
Should the formula in D2 takes into account if Table1[PO Ack.]="X" besides Table1[PO status]="Pending"?

M.

#### Black Arrow

##### New Member
Yes, the formula in B2 returns the count of unique companies

I didn't fully understand what you want in D2.
Should the formula in D2 takes into account if Table1[PO Ack.]="X" besides Table1[Status]="Pending"?

M.
Hi M,

Cell D2 should show the number of PO Acknowledgements that are past due. Past due only applies when the PO Issue date is 14 days ago or longer and the PO Acknowledgement still has an X.

I hope this helps.

#### Marcelo Branco

##### MrExcel MVP

Maybe...

D2
=SUMPRODUCT(--(MATCH(Table1[Company],Table1[Company],0)=ROW(Table1[Company])-MIN(ROW(Table1[Company]))+1),--(Table1[PO Ack.]="X"),--(Table1[PO issued]<\$A\$2-15))

M.

#### Black Arrow

##### New Member
Maybe...

D2
=SUMPRODUCT(--(MATCH(Table1[Company],Table1[Company],0)=ROW(Table1[Company])-MIN(ROW(Table1[Company]))+1),--(Table1[PO Ack.]="X"),--(Table1[PO issued]<\$A\$2-15))

M.
Hi M.

This did the trick! Thanks a lot!

#### Marcelo Branco

##### MrExcel MVP

This did the trick! Thanks a lot!

hmm...i'm not 100% sure if the formula is correct

Supposing E4 = "Delivered" what would be the expected result?
In other words, should the formula also takes into account Status = "Pending"?

M.

#### Black Arrow

##### New Member
hmm...i'm not 100% sure if the formula is correct

Supposing E4 = "Delivered" what would be the expected result?
In other words, should the formula also takes into account Status = "Pending"?

M.
Hi M.

That is not a problem, since the PO Status of not only linked to whether the PO Acknowledgement is received or not.

#### Marcelo Branco

##### MrExcel MVP
BlackArrow

Uff..the formula seems OK

M.

#### Black Arrow

##### New Member
BlackArrow

Uff..the formula seems OK

M.
Thanks again!

Replies
0
Views
208
Replies
31
Views
601
Replies
1
Views
295
Replies
2
Views
600
Replies
8
Views
493

1,141,618
Messages
5,707,430
Members
421,508
Latest member
someinternetuser

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back