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

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
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,210
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,210
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,210
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?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,508
Messages
5,854,136
Members
431,619
Latest member
AjaiSpillers

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