Countif

[undemane]

I have data as shown below. The key are "TYPE" and "TEST RESULTS". I want to know the count of how many tests were PV1 + PASS; and PV2 + PASS and count them in the far right two columns (I showed the counts manually - e.g., on 10/1/20, there are 3 PV2s that Passed). If the results say "NOK" then I skip that test.

I have "Date" going from 9/28 to 4/30/2021 and the data on the left going from A1:A600.

Type	PV1 Planned Complete	PV1 Actual Complete	PV1 NOK Res Date	PV2 Planned Complete	PV2 Actual Complete	PV2 NOK Res Date	Test Results		Date	PV1 PASS Actual Complete	PV2 PASS Actual Complete
PV2	9/29/2020	9/29/2020	1/0/1900	9/29/2020	9/29/2020	10/29/2020	PASS		9/28/2020
PV1	10/1/2020	10/1/2020	1/0/1900	1/0/1900	1/0/1900	1/0/1900	PASS		9/29/2020		1
PV2	9/28/2020	9/28/2020	1/0/1900	10/1/2020	10/1/2020	10/30/2020	PASS		9/30/2020
PV2	1/0/1900	1/0/1900	1/0/1900	10/1/2020	10/1/2020	10/31/2020	PASS		10/1/2020	1	3
PV2	1/0/1900	1/0/1900	1/0/1900	10/1/2020	10/1/2020	11/1/2020	PASS		10/2/2020
PV2	1/0/1900	1/0/1900	1/0/1900	10/28/2020	1/0/1900	11/4/2020	NOK		10/3/2020

 

[Fluff]

How about

+Fluff v2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Type	PV1 Planned Complete	PV1 Actual Complete	PV1 NOK Res Date	PV2 Planned Complete	PV2 Actual Complete	PV2 NOK Res Date	Test Results		Date	PV1 PASS Actual Complete	PV2 PASS Actual Complete
2	PV2	29/09/2020	29/09/2020	00/01/1900	29/09/2020	29/09/2020	29/10/2020	PASS		28/09/2020	0	0
3	PV1	01/10/2020	01/10/2020	00/01/1900	00/01/1900	00/01/1900	00/01/1900	PASS		29/09/2020	0	1
4	PV2	28/09/2020	28/09/2020	00/01/1900	01/10/2020	01/10/2020	30/10/2020	PASS		30/09/2020	0	0
5	PV2	00/01/1900	00/01/1900	00/01/1900	01/10/2020	01/10/2020	31/10/2020	PASS		01/10/2020	1	3
6	PV2	00/01/1900	00/01/1900	00/01/1900	01/10/2020	01/10/2020	01/11/2020	PASS		02/10/2020	0	0
7	PV2	00/01/1900	00/01/1900	00/01/1900	28/10/2020	00/01/1900	04/11/2020	NOK		03/10/2020	0	0
Main

Cell Formulas
Range	Formula
K2:K7	K2	=COUNTIFS(A:A,"PV1",C:C,J2,H:H,"Pass")
L2:L7	L2	=COUNTIFS(A:A,"PV2",F:F,J2,H:H,"Pass")

 

[C Moore]

Have you looked at the COUNTIFS function?

 

[undemane]

How about

+Fluff v2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Type	PV1 Planned Complete	PV1 Actual Complete	PV1 NOK Res Date	PV2 Planned Complete	PV2 Actual Complete	PV2 NOK Res Date	Test Results		Date	PV1 PASS Actual Complete	PV2 PASS Actual Complete
2	PV2	29/09/2020	29/09/2020	00/01/1900	29/09/2020	29/09/2020	29/10/2020	PASS		28/09/2020	0	0
3	PV1	01/10/2020	01/10/2020	00/01/1900	00/01/1900	00/01/1900	00/01/1900	PASS		29/09/2020	0	1
4	PV2	28/09/2020	28/09/2020	00/01/1900	01/10/2020	01/10/2020	30/10/2020	PASS		30/09/2020	0	0
5	PV2	00/01/1900	00/01/1900	00/01/1900	01/10/2020	01/10/2020	31/10/2020	PASS		01/10/2020	1	3
6	PV2	00/01/1900	00/01/1900	00/01/1900	01/10/2020	01/10/2020	01/11/2020	PASS		02/10/2020	0	0
7	PV2	00/01/1900	00/01/1900	00/01/1900	28/10/2020	00/01/1900	04/11/2020	NOK		03/10/2020	0	0
Main

Cell Formulas
Range	Formula
K2:K7	K2	=COUNTIFS(A:A,"PV1",C:C,J2,H:H,"Pass")
L2:L7	L2	=COUNTIFS(A:A,"PV2",F:F,J2,H:H,"Pass")

Thank you!

 

[Fluff]

You're welcome & thanks for the feedback.