Count cells in row Z that match criteria in row A

[Spikenaylor]

I need to count cells in row Z that match the criteria in Row A. This means I can easliy change the criteria (Row A) going forwards and not have to change the formula

My current Row Z contains Y and N, my current criteria is to count cells that are Y. however the way the crtieria has been written may change and soem of the Criteria may change to N, so I would have to count the Y and N. If my formula was to count cells in Row Z that each row matches the criteria value in Row A, then this would be some future proofing

at the moment my formula is COUNTIF(Z11:Z12,"Y")+(COUNTIF(Z14,"N"))

Row A - Criteria	Row Z - actual Result
Y	Y
Y	N

If I could get the formula to count Row Z where each row matched Row A, Then the Criteria could change at some date in the future, but the formulas would not need changing

hope this makes sense

Regards

 

[Toadstool]

Hi Spikenaylor,

I'm not sure if I'm correctly interpreting you challenge so please test if this work:

SpikeNaylor.xlsx
	A	B	C	D
1	Row A - Criteria	Row Z - actual Result		Result
2	Y	Y		2
3	Y	N
4	N	Y
5	N	N
6
Sheet1

Cell Formulas
Range	Formula
D2	D2	=SUMPRODUCT(--($A$2:$A$9999=$B$2:$B$9999)*($A$2:$A$9999<>""))

 

[Peter_SSs]

Since you have Microsoft 365 you could also use one of the new dynamic array functions available there.

21 06 10.xlsm
	A	B	C	D
1	Column A - Criteria	Column Z - actual Result		Result
2	Y	Y		2
3	Y	N
4	N	Y
5	N	N
6
Matches

Cell Formulas
Range	Formula
D2	D2	=COUNT(FILTER(ROW(B2:B1000),(B2:B1000=A2:A1000)*(B2:B1000<>""),""))