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

CANNONT4

New Member
Joined
Jan 6, 2021
Messages
4
Office Version
  1. 365
Platform
  1. MacOS
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.
 

Attachments

  • Screenshot 2021-01-06 at 19.11.18.png
    Screenshot 2021-01-06 at 19.11.18.png
    98.9 KB · Views: 4
  • Screenshot 2021-01-06 at 19.13.24.png
    Screenshot 2021-01-06 at 19.13.24.png
    181.4 KB · Views: 4

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

CANNONT4

New Member
Joined
Jan 6, 2021
Messages
4
Office Version
  1. 365
Platform
  1. MacOS
Apologies, I forgot to upload a screenshot with the formula!
 

Attachments

  • Screenshot 2021-01-06 at 19.17.05.png
    Screenshot 2021-01-06 at 19.17.05.png
    125.1 KB · Views: 3

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,231
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
Excel Formula:
=COUNTIFS(B5:V5,"Y",$B$2:$V$2,"Register")
 
Solution

CANNONT4

New Member
Joined
Jan 6, 2021
Messages
4
Office Version
  1. 365
Platform
  1. MacOS
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,231
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Jan 6, 2021
Messages
4
Office Version
  1. 365
Platform
  1. MacOS
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!
 

Attachments

  • Screenshot 2021-01-06 at 20.03.47.png
    Screenshot 2021-01-06 at 20.03.47.png
    59.6 KB · Views: 2

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,231
Office Version
  1. 365
Platform
  1. Windows
That's just pure coincidence.
If you put this in AA3 & drag down & across it would be safer.
Excel Formula:
=COUNTIFS($B3:$V3,"Y",$B$2:$V$2,AA$2)
 

Watch MrExcel Video

Forum statistics

Threads
1,126,932
Messages
5,621,676
Members
415,849
Latest member
PhoenixRising2015

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