Formula Increments for a Range

[BrandykinsGamer]

I have a formula that I would like to increase both cell #s by 3 each time the formula is dragged to the next row.
Let me give an example in case my explanation is not clear enough.
This is what I want it to do -
Cell 1: =countif(Overland!D9:Y11, true)
Cell 2: =countif(Overland!D12:Y14, true)
Cell 3: =countif(Overland!D15:Y17, true)

But right now it does this -
Cell 1: =countif(Overland!D9:Y11, true)
Cell 2: =countif(Overland!D10:Y12, true)
Cell 3: =countif(Overland!D11:Y13, true)

Is there a way this can be accomplished?

 

[jasonb75]

Try

=COUNTIF(OFFSET(Overland!$D$9,(ROWS(A$1:A1)-1)*3,0,3,COLUMNS(Overland!$D:$Y)),TRUE)

Where A$1:A1 refers to 'Cell 1' in your example.

 

[BrandykinsGamer]

I'm not sure I understand where to start this, so let me repost my above info with the actual cell #s so that I can follow along ... LOL

Overview!M5: =countif(Overland!D9:Y11, true)
Overview!M6: =countif(Overland!D12:Y14, true)
Overview!M7: =countif(Overland!D15:Y17, true)

 

[jasonb75]

In M5, then drag down, nothing needs to be changed.

=COUNTIF(OFFSET(Overland!$D$9,(ROWS(M$5:M5)-1)*3,0,3,COLUMNS(Overland!$D:$Y)),TRUE)

When you drag down it will change to M$5:M6. The rest of the formula remains unchanged, that is the part that does all of the work by identifying the top left corner of each range, D9:Y11,D12:Y14, etc. The rest sets a block of 3 rows by 22 columns starting from that point.

 

[BrandykinsGamer]

And how do I change that formula to be one that counts all items in the range instead of just the ones that are true?

 

[BrandykinsGamer]

Nvm. I think I figured that part out. Thank you so much for your help!