Edit excel countifs

I want to edit this query

=COUNTIFS('Sheet1'!E2:E465,7,'Sheet1'!F2:F465,2)

so that when I drag/copy it sideways, as in, next cell to the right the 2 stays constant and the 7 increments like

=COUNTIFS('Sheet1'!E2:E465,8,'Sheet1'!F2:F465,2开发者_如何学JAVA)

and so on etc

=COUNTIFS('Sheet1'!E2:E465,9,'Sheet1'!F2:F465,2)
=COUNTIFS('Sheet1'!E2:E465,10,'Sheet1'!F2:F465,2)

no luck with the $ next to the 7

---

=COUNTIFS('Sheet1'!$E$2:$E$465,Column() + 1,'Sheet1'!$F$2:$F$465,2)

Except that instead of Column() + 1, you should go Column() + WhateverNumberGivesYouTheRightAnswer

So if you are in the sixth column, and want the value 7, you would say Column() + 1 (as in 6 + 1). If you are in the ninth column, and want the value 12, you would say Column() + 3

This will work if you drag it across and down.

---

I don't think drag/copy magic can be done with literal values in formulas.

There are a couple of ways to fudge it - probably the easiest is to insert an 'index' row above your data and do something like this (assuming indices are in row 1 and you're putting this in G,H,...):

=COUNTIFS('Sheet1'!E2:E465,G1,'Sheet1'!F2:F465,2)

Otherwise, you could fudge it buy using the COLUMN() function to get the current column and working out the 7,8,9... sequence from that. This will break though if you move the formula around in the worksheet.