Count first instance of duplicate value with multiple criteria

[Legacy 469343]

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]

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.

 

[Legacy 469343]

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]

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.

 

[Legacy 469343]

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]

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.

 

[Legacy 469343]

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]

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.

 

[Legacy 469343]

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]

BlackArrow

Uff..the formula seems OK

You're welcome. Glad to help.

M.

 

[Legacy 469343]

BlackArrow

Uff..the formula seems OK

You're welcome. Glad to help.

M.

Thanks again!