# Count cells in row Z that match criteria in row A

#### Spikenaylor

##### Board Regular
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

### Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

##### Well-known Member
Hi Spikenaylor,

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

SpikeNaylor.xlsx
ABCD
1Row A - CriteriaRow Z - actual ResultResult
2YY2
3YN
4NY
5NN
6
Sheet1
Cell Formulas
RangeFormula
D2D2=SUMPRODUCT(--(\$A\$2:\$A\$9999=\$B\$2:\$B\$9999)*(\$A\$2:\$A\$9999<>""))

#### Peter_SSs

##### MrExcel MVP, Moderator
Since you have Microsoft 365 you could also use one of the new dynamic array functions available there.

21 06 10.xlsm
ABCD
1Column A - CriteriaColumn Z - actual ResultResult
2YY2
3YN
4NY
5NN
6
Matches
Cell Formulas
RangeFormula
D2D2=COUNT(FILTER(ROW(B2:B1000),(B2:B1000=A2:A1000)*(B2:B1000<>""),""))

Replies
2
Views
95
Replies
1
Views
200
Replies
9
Views
230
Replies
8
Views
464
Replies
10
Views
91

1,141,072
Messages
5,704,133
Members
421,329
Latest member
mippy

### 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.

### Which adblocker are you using?

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

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