every nth row count

Question

This is a repeat because my 1st post was stupidly written.

I want to count every 4th row ONLY if it has a date in it) starting with D7, ie;   (D7, D11, D15, D19  skips D23 because no date , D27) so far the answer would be 5.

Count each sell as 1 and total as it counts.

Range D7:D1000

Thanks and I'm sorry for the repeat.

Mark

Answer 1

If, by having a date means not empty:

=SUMPRODUCT((MOD(ROW(D7:D1000)-6,4)=1)*(D7:D1000<>""))

If, the cells could have text and not have a date:

=SUMPRODUCT((MOD(ROW(D7:D1000)-6,4)=1)*(D7:D1000<>"")*NOT(ISERROR(WEEKDAY(D7:D1000))))

Answer 2

Format the cell with the formula as general or as a number with no decimal places. It is formatted as a date, and you are using the 1904 date system.

Answer 3

These formulas in this response gives me an answer of 1/2/1904 )(In S5)when I enter a date in D7 of 1/1/2013.

I want it to return the answer "1" and then 2 if there is a date in D11. etc.

Don't count cells with no date.

Maybe I am not applying the formula correctll.

I APOLOGISE for my ignorance

CORRECTION:  These work if I hit my designated cell and Hit paste special.  Is that the way I get the formula in there?

Thanks for the answers guys, i'm not sure how I get myself in these messes.

Answer 4

Based on your previous post and Mike H.'s response in which he just didn't catch the need to test for a date in D, both of these work for me:

=SUMPRODUCT(--(MOD(ROW(D7:D1000),4)=3),--(D7:D1000>0))

or

=SUMPRODUCT(--(MOD(ROW(D7:D1000),4)=3),--(D7:D1000<>""))

Bernie's works also (but that's no surprise).