how to drag and copy a formula by skipping rows

**loboexcel** · 12-16-2012, 11:21 AM

I am asking for help for the following issues:
I have in cell D206 the following formula =COUNTIF(D8:AE8,">=3")
I would like to drag down and copy this formula under the next 40 cell bellows.
But I do not want to obtain the following result:
In Cell D207 ___ =COUNTIF(D9:AE9,">=3")
In Cell D208 ___ =COUNTIF(D10:AE10,">=3")
In Cell D209 ___ =COUNTIF(D11:AE11,">=3")
In Cell D210 ___ =COUNTIF(D12:AE12,">=3")
And so on (Rows are incremented by one)
I would like to obtain the following result when I drag down the formula by skipping each row by 8:
In Cell D207 ___ =COUNTIF(D16:AE16,">=3")
In Cell D208 ___ =COUNTIF(D24:AE24,">=3")
In Cell D209 ___ =COUNTIF(D32:AE32,">=3")
In Cell D210 ___ =COUNTIF(D40:AE40,">=3")
And so on
Your help is much appreciated
Thank you

**Ron Coderre** · 12-16-2012, 11:56 AM

This regular formula, copied down, mimics that behavior:
D206: =COUNTIF(INDEX(D:D,ROWS($206:206)*8):INDEX(AE:AE,ROWS($206:206)*8),">=3")

Is that something you can work with?

**loboexcel** · 12-16-2012, 04:43 PM

With your help I start getting the idea how index work. I tried to use your suggestion but did not work.

I am not an expert in this matter, but I notice in your answer that you mention to use the data from cell D206 in the same cell D206 formula (it seems to me that the cell is trying to use the data from itself). I would like to use the data from cell D16, D24, D32 and so on and AE16, AE24, AE32 and so on.

I appreciate your help in this matter

**FDibbins** · 12-16-2012, 04:58 PM

I think what Ron meant was that his formula was to be entered IN D206

**loboexcel** · 12-17-2012, 03:44 AM

Thank you gentlemen for helping me in my Excel Odyssey.
I believe I am not presenting my problem correctly since your guidance is not giving me the result desire….. so blame the messenger (myself).
Let me see if I express my problem in another example may give clarity to my difficulty:
Let say I have the following data in the following cells (array):
In the first row I have A1:3, in D1:2 and in G1:5.
In the second row I have A4:5, in D4:3 and G4:4.
This will continue in several rows, always skipping two rows.
Then I have in cell A28: =COUNTIF(A1:G1,">=3")
Then I would like to drag/copy down cell A28 and I would like each new cell/formula to take the from the following row above. In another word I expect the following result in each cell:
A28: =COUNTIF(A1:G1,">=3")
A29: =COUNTIF(A4:G4,">=3")
A30: =COUNTIF(A7:G7,">=3")
This will continue in several rows.
I have a large data which entering this formula manually will take long time.
Your help it is appreciated
Thank you very much in advance

**Ron Coderre** · 12-17-2012, 10:07 AM

Same principle as my previous post...

Please Login or Register  to view this content.

Copy that formula down as far as you need.

**loboexcel** · 12-17-2012, 10:35 AM

Oh man….. you are an Excel Guru!!
The formula works!!! Eureka!!!
Thank you very much !!!! and Happy Holidays!!!

**Ron Coderre** · 12-17-2012, 11:18 AM

You're very welcome...I'm glad I could help.

how to drag and copy a formula by skipping rows

LinkBack

Thread Tools

Rate This Thread

Display

how to drag and copy a formula by skipping rows

Re: how to drag and copy a formula by skipping rows

Re: how to drag and copy a formula by skipping rows

Re: how to drag and copy a formula by skipping rows

Re: how to drag and copy a formula by skipping rows

Re: how to drag and copy a formula by skipping rows

Re: how to drag and copy a formula by skipping rows

Re: how to drag and copy a formula by skipping rows

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions