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.

 

[rondeondo]

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.

 

[Legacy 469343]

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
	A	B	C	D	E	F
1	Date	PO Acknowledgement	PO Acknowledgement Overdue
2	7/4/2021
3	Company	PO issued	PO	Item	PO status	PO Acknowledgement
4	Company 1	12-Jun	111	2	Status 1	X
5	Company 2	15-Jun	222	1	Status 2	X
6	Company 2	15-Jun	222	2	Status 2	X
7	Company 2	15-Jun	222	3	Status 2	X
8	Company 2	15-Jun	222	4	Status 2	X
9	Company 2	15-Jun	222	5	Status 2	X
10	Company 2	15-Jun	222	6	Status 2	X
11	Company 3	30-Jun	333	1	Status 3	X
12	Company 3	30-Jun	333	2	Status 3	X
13	Company 3	30-Jun	333	3	Status 3	X
14	Company 3	30-Jun	333	4	Status 3	X
15	Company 3	30-Jun	333	5	Status 3	X
Sheet1

Cell Formulas
Range	Formula
A2	A2	=TODAY()

 

[rondeondo]

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

 

[Legacy 469343]

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]

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

 

[Legacy 469343]

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]

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.

 

[Legacy 469343]

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?

 

[Legacy 469343]

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
	A	B	C	D	E	F
1	Date	PO's	PO Ack.	PO Ack. Overdue
2	6-Jul-21	3	0
3	Company	PO issued	PO	Item	PO status	PO Ack.
4	Company 1	1-Jun-21	111	2	Pending	X
5	Company 2	5-Jun-21	222	1	Delivered	X
6	Company 2	5-Jun-21	222	2	Delivered	X
7	Company 2	5-Jun-21	222	3	Delivered	X
8	Company 3	1-Jul-21	333	1	Pending	X
9	Company 3	1-Jul-21	333	2	Pending	X
10	Company 3	1-Jul-21	333	3	Pending	X
Sheet1

Cell Formulas
Range	Formula
A2	A2	=TODAY()
B2	B2	=SUM(IF(FREQUENCY($C$4:$C$10,$C$4:$C$10)>0,1))
C2	C2	=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.