How to force excel to drag formula by two, rather than one.

[sasiddiq]

This is my formula:
=DCOUNT(timeg15w!$A$1:$F$3909, "WPC",Criteria!A1:C2)

My question concentrates on this part of the formula:...Criteria!A1:C2

When I drag down the formula, it changes, as usually, adding 1 to the cells address, like this:
Criteria!A1:C2
Criteria!A2:C3
Criteria!A3:C4

BUT, I want the formula dragged down, adding 2 instead of 1, changing like this:
Criteria!A1:C2
Criteria!A3:C4
Criteria!A5:C6

How can I achieve this result?

[Ron Coderre]

Try this kind of approach:

Please Login or Register  to view this content.

Instead of starting with ROWS($1:1)
reference the row that the first dcount formula is in.

When copied down, that formula will increment the criteria range by 2 rows.

Is that something you can work with?

[sasiddiq]

Yes, that works perfectly!! Thank you so much. Just so I am clear that I understand this formula. If I wanted To utilize the same formula and apply it to a starting criteria of
Criteria!D1:G2
Criteria!D3:G4
Criteria!D5:G6

Would I modify the formula to:

=DCOUNT(timeg15w!$A$1:$F$3909, "WPC",OFFSET(Criteria!$D$1:$G$2,(ROWS($1:1)-1)*2,))

Please advise if this would work and thank you so much for your help!!

[Ron Coderre]

Yes, if the first of those formulas is in Row_1.
Technically, the formula can be in another row and reference Row_1. However, that leaves it vulnerable to corruptions caused by somebody inserting/deleting rows.

So, if the formula begins in L7 use this:

Please Login or Register  to view this content.