progress a column range when copying

[ratchet1369]

Hello all,

I'm using a =countifs() to count how many students got an answer right on a quiz. In my submission table, the answer to question 1 is in column G and the answer to question 2 is in column H and so forth. When I copy the formula down (vertically), the column (initially G) isn't progressing to H, I, J, and so forth. I'd like it to progress to the next letter automatically rather than update 37 formulas.

=countifs('sheet1'!G:G,'Sheet2'!$B2)

The row for B2, moves on to B3 since I'm copying down but the G:G remains constant. I was trying a =row() to get the G:G to progress through the alphabet but that returns a number value.

Appreciate the help or link to the answer for a prior post. I wasn't able to find a prior post.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)