# change column letter in COUNTIF and SUM formulas while dragging down

#### prismatick

##### New Member
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

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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)))

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!

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.

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!

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?

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.

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?

Replies
11
Views
531
Replies
3
Views
1K
Replies
3
Views
453
Replies
3
Views
192
Replies
4
Views
137

1,203,073
Messages
6,053,379
Members
444,660
Latest member
Mingalsbe

### 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.

### Which adblocker are you using?

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

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