Count first instance of duplicate value with multiple criteria

Black Arrow

New Member
Joined
Dec 1, 2020
Messages
39
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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.

1625133009870.png
 
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.
 
Upvote 0

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
Solution
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,700
Members
448,293
Latest member
jin kazuya

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top