# 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

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

#### Toadstool

##### 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
66
Replies
8
Views
387
Replies
9
Views
56
Replies
3
Views
73
Replies
6
Views
333

Threads
1,136,909
Messages
5,678,514
Members
419,768
Latest member
eguechi09x

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

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