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

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
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)))
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,188
Members
448,554
Latest member
Gleisner2

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
Back
Top