Count cells in row Z that match criteria in row A

Spikenaylor

Board Regular
Joined
Apr 14, 2013
Messages
111
Office Version
  1. 365
Platform
  1. Windows
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 - CriteriaRow Z - actual Result
YY
YN

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.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,913
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
May 28, 2005
Messages
50,527
Office Version
  1. 365
Platform
  1. Windows
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<>""),""))
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

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
Top