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
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,867
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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
Joined
Dec 1, 2020
Messages
39
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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
Joined
Aug 23, 2010
Messages
16,867
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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
Joined
Dec 1, 2020
Messages
39
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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
Joined
Aug 23, 2010
Messages
16,867
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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.
 
Solution

Black Arrow

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

ADVERTISEMENT

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
Joined
Dec 1, 2020
Messages
39
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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
Joined
Aug 23, 2010
Messages
16,867
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
BlackArrow

Uff..the formula seems OK :)

You're welcome. Glad to help.

M.
 

Forum statistics

Threads
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.
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
Top