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
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

rondeondo

Board Regular
Joined
Aug 15, 2012
Messages
188
Office Version
  1. 365
Platform
  1. Windows
Hi,
I'd recommend countifs(), note the S, this allows for multiple range/criteria pairs and treats them with logical AND.
I'm too lazy to re-type your example data from an image, please use the L2BB function to paste sample data.
 

Black Arrow

New Member
Joined
Dec 1, 2020
Messages
39
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Hi,
I'd recommend countifs(), note the S, this allows for multiple range/criteria pairs and treats them with logical AND.
I'm too lazy to re-type your example data from an image, please use the L2BB function to paste sample data.

Hi rondeondo,

Here you go:

Test.xlsx
ABCDEF
1DatePO AcknowledgementPO Acknowledgement Overdue
27/4/2021
3CompanyPO issuedPOItemPO statusPO Acknowledgement
4Company 112-Jun1112Status 1X
5Company 215-Jun2221Status 2X
6Company 215-Jun2222Status 2X
7Company 215-Jun2223Status 2X
8Company 215-Jun2224Status 2X
9Company 215-Jun2225Status 2X
10Company 215-Jun2226Status 2X
11Company 330-Jun3331Status 3X
12Company 330-Jun3332Status 3X
13Company 330-Jun3333Status 3X
14Company 330-Jun3334Status 3X
15Company 330-Jun3335Status 3X
Sheet1
Cell Formulas
RangeFormula
A2A2=TODAY()
 

rondeondo

Board Regular
Joined
Aug 15, 2012
Messages
188
Office Version
  1. 365
Platform
  1. Windows
Hi Black Arrow,

If I've understood correctly:
B2: =COUNTIF(F:F,"X")
C2: =COUNTIFS(F:F,"X",B:B,"<"&$A$2-14,G:G,"1")
G4:g15: =COUNTIFS(A$3:A4,A4,C$3:C4,C4)

With the data given, this gives B2=12 and c2=2
 

Black Arrow

New Member
Joined
Dec 1, 2020
Messages
39
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

Hi Black Arrow,

If I've understood correctly:
B2: =COUNTIF(F:F,"X")
C2: =COUNTIFS(F:F,"X",B:B,"<"&$A$2-14,G:G,"1")
G4:g15: =COUNTIFS(A$3:A4,A4,C$3:C4,C4)

With the data given, this gives B2=12 and c2=2
Hi rondeondo,

Unfortunately this is not what I need. Data given should be:
B2 = 3, since there are only 3 PO's and it needs to count every PO acknowledgement once for every PO, regardless of the amount of line items per PO.
C2 = 2, since there are 2 PO Acknowledgements overdue, it needs to count 2 PO Acknowledgements overdue.

I hope this helps.
 

rondeondo

Board Regular
Joined
Aug 15, 2012
Messages
188
Office Version
  1. 365
Platform
  1. Windows
Hi Black Arrow
I see, make B2 the same as C2 but without the middle two arguements:
B2: =COUNTIFS(F:F,"X",G:G,"1")

The countifs allows as many sets of range,criteria arguements as you like, the ranges have to be the same sizes
 

Black Arrow

New Member
Joined
Dec 1, 2020
Messages
39
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

Hi Black Arrow
I see, make B2 the same as C2 but without the middle two arguements:
B2: =COUNTIFS(F:F,"X",G:G,"1")

The countifs allows as many sets of range,criteria arguements as you like, the ranges have to be the same sizes
Hi rondeondo,

What is supposed to be in the G column?
 

rondeondo

Board Regular
Joined
Aug 15, 2012
Messages
188
Office Version
  1. 365
Platform
  1. Windows
as per my earlier answer, G4:g15: =COUNTIFS(A$3:A4,A4,C$3:C4,C4)
this gives a 1 where it's the first line with the same company and PO.
 

Black Arrow

New Member
Joined
Dec 1, 2020
Messages
39
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
as per my earlier answer, G4:g15: =COUNTIFS(A$3:A4,A4,C$3:C4,C4)
this gives a 1 where it's the first line with the same company and PO.
I see, do you think it is possible to incorporate the step of column G into a formula?
 

Black Arrow

New Member
Joined
Dec 1, 2020
Messages
39
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
I see, do you think it is possible to incorporate the step of column G into a formula?
Hi rondeondo,

I used a formula from another sheet I have, but it does not do what I want it to do. Maybe you know how to change this and then I think we have it!

Test.xlsx
ABCDEF
1DatePO'sPO Ack.PO Ack. Overdue
26-Jul-2130
3CompanyPO issuedPOItemPO statusPO Ack.
4Company 11-Jun-211112PendingX
5Company 25-Jun-212221DeliveredX
6Company 25-Jun-212222DeliveredX
7Company 25-Jun-212223DeliveredX
8Company 31-Jul-213331PendingX
9Company 31-Jul-213332PendingX
10Company 31-Jul-213333PendingX
Sheet1
Cell Formulas
RangeFormula
A2A2=TODAY()
B2B2=SUM(IF(FREQUENCY($C$4:$C$10,$C$4:$C$10)>0,1))
C2C2=SUM(--(FREQUENCY(Table1[PO],Table1[PO])-FREQUENCY(IF(Table1[PO Ack.]="X",Table1[PO]),Table1[PO])>0))
Press CTRL+SHIFT+ENTER to enter array formulas.


The only issue with the formula in C2, it counts one PO Acknowledgement when the X for all items is filled in, which is good. But it should be the other way around. If all the X's for the items are filled in it should count one PO Acknowledgement. So in the upper mini sheet it should count 3. But if we would remove the X behind PO 222, item 1, C2 should count 2.
 
Last edited:

Forum statistics

Threads
1,141,062
Messages
5,704,057
Members
421,325
Latest member
tapete86

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