# 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

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

#### 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
13
Views
222
Replies
2
Views
96
Replies
1
Views
209
Replies
9
Views
247
Replies
8
Views
467

Threads
1,141,413
Messages
5,706,301
Members
421,441
Latest member
VapesRub

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