No Tom it didn't As you can see by the date 23/01/2009 I tried it with my
clock changed. There was no reply so I thought you'd given up on me, which I
couldn't blame you, I would have.

Bryan.

"Bryan De-Lara" wrote in message
...
Am I the first to send a message 23/01/2009?


"Bryan De-Lara" wrote in message
...
That's good Tom, it works across now when I drag the formula.
There is one problem, I've changed my clock to simulate next year. I have
put two 1's together then a single 1 and another single 1. This before
the Today formula would have read so...
s s d total points
3 3 4 36

After with date changed
s s d total points
2 2 4 16 when it should read 2 2 2 = 8 then
first and second s is correct, it still counts the total days. I think
that is because of =SUM(D6:D2936) which is in D2948. I put it that low so
as not to cause the circular ref and it gave me space to add something
there without have to insert cells. Should I move it to the top?

Bryan.

p s what does the $ do in ),--($A4:$A2936=TODAY()-365)), I take it, it
stops the A from changing to B etc.



"TomPl" wrote in message
...
Good evening Bryan,

the count of occurances.
=SUMPRODUCT(--(D4:D2936=1),--(D5:D2937<1),--(A4:A2936=TODAY()-365))
=SUMPRODUCT(--(F4:F2936=1),--(F5:F2937<1),--(C4:C2936=TODAY()-365))
The date column must refer to column A. Try this.
=SUMPRODUCT(--(D4:D2936=1),--(D5:D2937<1),--($A4:$A2936=TODAY()-365))

the count of days.
Based on the above formula, I will assume that your data goes between
rows 4 and 2936. Row 2937 should remain blank.
If your formula is in row 2936 (as you indicated) it has a circular
reference.
That is not good.
Your formula should be:
=SUMPRODUCT(D4:D2936,--($A4:$A2936=TODAY()-365))
And I assume that it is located in row 2938 to avoid the circular
reference.
That should give you the total number of "1"s during the last 365 days.

Let me know if this works.

Tom