ansaurus

Question

Edit excel countifs

Answer 1

A:

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.

Aaron Lockey 2009-12-04 01:11:00

Cheers. I was thinking about using the index work around you've described but was hoping I could just use the copy magic :)

baron 2009-12-04 01:20:14

Answer 2

A:

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

ScottF 2009-12-04 01:20:43

ansaurus

tags:

views:

answers:

Edit excel countifs

related questions