how to autofill cells when using a sum,countif, indirect function

[CANNONT4]

Hi all,

I am trying to create a spreadhseet that records how many times the letter y is input into multiple cells across the sheet. I have managed to do this however when I drag the little square down in the corner of the cell to duplicate the formula with the automatic changes (e.g. b5 to b6 then b7 and so on) it just duplicates the formula, when i click to flash fill it says it cannot do it and comes up with the pictured error. Please can you help me to fix this! Im a novice at best and totally out of my depth so it would be greatly appreciated!

Tom.

 

[CANNONT4]

Apologies, I forgot to upload a screenshot with the formula!

 

[Fluff]

Hi & welcome to MrExcel.
How about

=COUNTIFS(B5:V5,"Y",$B$2:$V$2,"Register")

 

[CANNONT4]

I don't know how you've done it, I don't understand the formula beyon searching the range for y, not sure why register is in the formula. But I dont care, because it works!

Thanks so much, also for being so prompt!

 

[Fluff]

not sure why register is in the formula.

It's counting the cells that have Y if the column header (row2) says Register.

Glad to help & thanks for the feedback.

 

[CANNONT4]

It's counting the cells that have Y if the column header (row2) says Register.

Glad to help & thanks for the feedback.

I wasn't expecting a reply so thank you. How come in that case it works for all the other columns to, LLC task for example, I've attached a screenshot. This is what I wanted it to do so it's working exactly as needed but I'm just interested!

 

[Fluff]

That's just pure coincidence.
If you put this in AA3 & drag down & across it would be safer.

=COUNTIFS($B3:$V3,"Y",$B$2:$V$2,AA$2)