How to copy a formula down and skip n cells each time.

**SIGG** · 08-29-2010, 12:16 AM

I have a formula, =countif('JSPC''s'!E5:E18,"E")

It references the previous worksheet.

I want to copy this formula down the next 31 rows, but I need to to skip down several cells from the JSPC sheet where it is copying from. It must do this each of the next 31 times.

For example the next formual would count from cells E25:E38, the next from E45:E58 so on so forth.

I'm very new with excel and have been looking for a while with no luck on a solution. any help would be appreciated, as I need to do the same thing for a total of 51 columns.

(Office 2007)

**DonkeyOte** · 08-29-2010, 01:56 AM

You don't specify in which cell the first formula appears ... for sake of example let's assume it is B2

Please Login or Register  to view this content.

or, if you prefer you can use an OFFSET approach

Please Login or Register  to view this content.

though the above has simpler syntax it is Volatile unlike the INDEX approach
(for more info. on Volatility see the link in my sig.)

If (as I suspect) the first formula does not appear in B2 modify the references to B2 accordingly though note use of $ in the existing references.

**SIGG** · 08-29-2010, 05:14 AM

Ok, I got the formula to copy down and it works, with one problem. I should have made this more specific; I dont want to have a running count (adding all the instances of "E" on top of the previous formuals. I only want excel to spit out the number of "E's" that occur in each range.

So range E5:E18 would count the E's in that range and put them in B:4 of the next page.
The way I have it now it does that for E5:E18 but when it goes to the next range it adds the first (E5:E18) to E25:E38. I want to keep each total seperate.

I appreciate the help as I'm a complete novice at this.

If it helps at all, page one has a bunch of individual Job Performance Summary Cards, so each person has there own cell for how many "E's" they receive in a particular category on the second page.

**DonkeyOte** · 08-29-2010, 06:30 AM

Neither formula provided return a cumulative total when copied (each applies to a specific range E5:E18, E25:E38 etc...)

**Marcol** · 08-29-2010, 07:18 AM

Don

I think you might have missed a multiplier
B2:
=COUNTIF(INDEX('JSPC''s'!E:E,5+20*(ROWS(B$2:B2)-1)):INDEX('JSPC''s'!E:E,18+20*(ROWS(B$2:B2)-1)),"E")

**DonkeyOte** · 08-29-2010, 01:44 PM

apologies all - thanks for the catch Marcol
(the OFFSET would have worked however .... )

**SIGG** · 08-30-2010, 01:09 AM

This worked beautifully, and saved me an enormous amount of time. Thanks DonkeyOte and Marcol!

How to copy a formula down and skip n cells each time.

LinkBack

Thread Tools

Rate This Thread

Display

How to copy a formula down and skip n cells each time.

Re: How to copy a formula down and skip n cells each time.

Re: How to copy a formula down and skip n cells each time.

Re: How to copy a formula down and skip n cells each time.

Re: How to copy a formula down and skip n cells each time.

Re: How to copy a formula down and skip n cells each time.

Re: How to copy a formula down and skip n cells each time.

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions