change column letter in COUNTIF and SUM formulas while dragging down

prismatick

New Member
Joined
May 30, 2015
Messages
4
Hi everyone,

I'm trying to use the COUNTIF function and SUM formulas to add up data on a second sheet and count the frequency of responses. I've had to write in each letter for the columns individually because they don't autopopulate as I drag them down, which is taking too much time to get the project done effectively.

Do you know how I can take the formulas
=COUNTIF(Sheet2!A:A,1) and =SUM(Sheet2!A:A)
and make the A change to B, C, D etc as I drag the formula down the column?

Thank you so much for your help!
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
A1, copy down:

=COUNTIFS(INDEX(Sheet2!$A:$Z,0,ROWS($A$1:A1)),1)

B1, copy down:

=SUM(INDEX(Sheet2!$A:$Z,0,ROWS($B$1:B1)))
 

prismatick

New Member
Joined
May 30, 2015
Messages
4
A1, copy down:

=COUNTIFS(INDEX(Sheet2!$A:$Z,0,ROWS($A$1:A1)),1)

B1, copy down:

=SUM(INDEX(Sheet2!$A:$Z,0,ROWS($B$1:B1)))

Thanks! I'm trying to use it on the real spreadsheet now and unfortunately not getting the right number.

The code would be
=COUNTIF('Values - QA'!PZG:PZG,1) - how can I modify this to work like the code above? I tried
=COUNTIFS(INDEX('Values - QA'!$A:$Z,0,ROWS($PZG$1:PZG3)),1)
and it gave me a much bigger number than the correct one.

Thank you!
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
Thanks! I'm trying to use it on the real spreadsheet now and unfortunately not getting the right number.

The code would be
=COUNTIF('Values - QA'!PZG:PZG,1) - how can I modify this to work like the code above? I tried
=COUNTIFS(INDEX('Values - QA'!$A:$Z,0,ROWS($PZG$1:PZG3)),1)
and it gave me a much bigger number than the correct one.

Thank you!

Try to adapt the suggestion which the following workbook implements: https://dl.dropboxusercontent.com/u...TIF and SUM formulas while dragging down.xlsx.
 

prismatick

New Member
Joined
May 30, 2015
Messages
4

ADVERTISEMENT

Thank you! I'm so sorry, it is still not working. I don't really understand how to modify it for the worksheets I'm working on. What does each part of the formula signify? They might help as the tables on my worksheet and data both don't start at A1 and I think that might be causing some of the issues.

Thanks again for all of your help - I really appreciate it!
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
Thank you! I'm so sorry, it is still not working. I don't really understand how to modify it for the worksheets I'm working on. What does each part of the formula signify? They might help as the tables on my worksheet and data both don't start at A1 and I think that might be causing some of the issues.

Thanks again for all of your help - I really appreciate it!

Doesn't the suggestion do what yous asked for:

"Do you know how I can take the formulas
=COUNTIF(Sheet2!A:A,1) and =SUM(Sheet2!A:A)
and make the A change to B, C, D etc as I drag the formula down the column?"

That is, when the formula is copied down, it process next column?
 

prismatick

New Member
Joined
May 30, 2015
Messages
4
It does for those columns, but when I try to change if for the actual spreadsheet I'm using, it doesn't. It displays #REF or larger numbers instead as I drag it down. I'm not sure why is stops working as it continues.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
It does for those columns, but when I try to change if for the actual spreadsheet I'm using, it doesn't. It displays #REF or larger numbers instead as I drag it down. I'm not sure why is stops working as it continues.

Where do you have the data, that is, from which column to which column?
 

Watch MrExcel Video

Forum statistics

Threads
1,129,752
Messages
5,638,162
Members
417,011
Latest member
Amaden95

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