Deleting by date automatically

  • Thread starter Thread starter Bryan De-Lara
  • Start date Start date
Bryan,

You indicated that the formula in cell D2948 is:
=SUM(D6:D2936)

To exclude stale "1"s the formula should be:
=SUMPRODUCT(--(D5:D2936=1),--($A5:$A2936>=TODAY()-365))

The dollar signs prevent the "A" from changing to "B", "C", "D" when you
copy the formula accross the row.

I like these types of formulas at the top of the data but that is up to you.
As long as it works, and I hope it does.

Tom
 
Tom,

How can I thank you enough. Everything works greaaat thanks. Without the
help of people like yourself, there would be a lot of frustrated people.
I must admit though, I've been dong this project for quite sometime, and I
do try my best with the help pages etc, but there are just some things that
we need help with.
No doubt when I start my next project I will need more help.
Thanks a million times for your time.

Bryan.

P.S. All my questions have been answered and completed.
 
Tom, I have been playing today with my nice new workbook. One thing I forgot
to do is to try and put holidays on the sheet, sometimes people take half
days so I tried to modify the formula from =SUMPRODUCT(--(D4:D2935="H")) to
=SUMPRODUCT(--(D4:D2935="H"))--SUMPRODUCT(--(D4:D2935="HD"=.5)) but it
registers 2 instead of 1.5. Can you tell me where I am going wrong please.
Bryan.
 
Look in Excel help for the syntax of the functions you are using, and look
at the syntax of the formula sugested earlier. Think about what the formula
is doing, and compare that with what you want it to do.

If you are struggling to debug your formula, break it up into manageable
chunks and see what each part is doing.
You could put =SUMPRODUCT(--(D4:D2935="H")) in H1,
=SUMPRODUCT(--(D4:D2935="HD"=0.5)) in H2, and =H1--H2 in H3.
If it still isn't making sense to you, look at syntax of each of those in
turn.
 
Thanks David, I did play with it again yesterday and found this works,
=SUMPRODUCT(--(D4:D2035="d"))--(--SUMPRODUCT(--(D4:D2035="HD"))*0.5)
Not exactly the same as yours, but at least I managed it...thanks.

Bryan.
 
Well, as I am not that good, or should I say that well up on Excel I didn't
know that you could use + between, another snippet filed for future use,
Thank you.

Bryan.
 
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.
 
Am I the first to send a message 23/01/2009?


Bryan De-Lara said:
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.
Click to expand...
 
Back
Top