Formula Error

[hmsawyer]

I have the following formula in place in a spreadsheet and have for the last
6 months. I just noticed a problem.

This formula is counting occurrences that have happened in the last 6 months
from TODAY(), but not counting anything that occurred in the first 90 days
from hire date, which is the beginning point in column A.

Now that some occurrences are 6 months old, the formula is counting those
that occurred in the first 90-days as a negative number. It should just not
count them at all, just as it did for the first 6 months they were listed on
the spreadsheet. Help! Thank you.

=IF($A3="",0,SUMIF(D3:IS3,">="&DATE(YEAR(TODAY()),MONTH(TODAY())-6,DAY(TODAY())),F3:IU3)-SUMIF(D3:IS3,"<"&($A3+90),F3:IU3))

 

[Pete_UK]

Can't you just omit the final SUMIF term? i.e.:

=IF($A3="",
0,SUMIF(D3:IS3,">="&DATE(YEAR(TODAY()),MONTH(TODAY())-6,DAY(TODA­
Y())),F3:IU3))

Hope this helps.

Pete

 

[hmsawyer]

Unfortunately no, b/c it should only be adding up occurances that happened
between 90 days after the date in column A and within 6 months from TODAY().

Pete, I don't know if you remember this or not, but you pretty much put this
together for me about 6 months ago. Again, any help you or anyone else can
offer is appreciated. Thanks again.

 

[Sheeloo]

If you are counting occurences then shouldn't you be using COUNTIF function...

Your formula is adding up the dates in the range F3:IU3 if date in D3:IS3 is
any date in the past six months or any date in future...
Then is substracting the sum of dates which are earlier than (hire date + 90
days). It will sum up even those dates which are earlier than hire date...

What do you really want? What do you want to achieve by summing dates?

 

[hmsawyer]

It should only be adding up occurances that happened between 90 days after
the date in column A and within 6 months from TODAY(). Thank you.

 

[Pete_UK]

Well, your name looked vaguely familiar but I didn't recall giving you that
formula, so I checked into all your previous posts - a lot of other people
contributed to your posts (so you can't blame it all on me !! <bg>). And
shame on you - there were a lot of posts where you did not feed back, so it
is uncertain whether you found the solutions workable.

I have difficulty visualising from your formula how your data is laid out.
You are checking the range D3:IS3 to see if it matches the criteria, and
then are summing from F3:IU3, i.e. offset by 2 cells. So, do you have
<date>, <something else>, <number> repeated across the row from column D
onwards?

I'm not sure what the timespan is that you want to sum over. It should be 90
days after the date in A3, but within 6 months from TODAY. So if A3 is 1st
Jan 08, and today is 1st Nov 08 (say), you want to count from 1st May 08 up
to today? Or do you want the time period from 30th Mar 08 (A3 + 90) up to
1st May 08 (6 months before today)?

Pete

 

[joeu2004]

It should only be adding up occurances that happened between
90 days after the date in column A and within 6 months from
TODAY().

Does the following work for you?

=sumproduct(--(D3:IS3 > $A3+90), --(D3:IS3 >= edate(today(), -6)),
F3:IU3)

EDATE is in the Analysis ToolPak. Use Excel Help for instructions
about how to install it if you get a #NAME error.

If you do not want to use EDATE, you could use:

date(year(today()), month(today())-6, day(today()))

(I would prefer to put =TODAY() into a helper cell.)

But I don't know if the DATE expression will work the way you want if
today's day is 29, 30 or 31 and the month is Aug 2009, for example.

 

[hmsawyer]

Pete,

I'm not blaming anything on you, quite the opposite! I wouldn't have gotten
this far without your help. You and I traded a bunch of emails about it and
you helped me out a ton with this workbook. Its used for tracking attendance
policy infractions. Remember?

Thanks again.

 

[Pete_UK]

Ah yes, Holly, how could I have forgotten? I've just looked up the emails we
exchanged (I rarely delete anything, just move them to other folders !!).

If you like we could continue this off-line again, but you will need to
explain the time-frame that you want the formula to operate over. Using a
few example dates, can you point out how you want the formula to perform?

Nice to hear from you again.

Pete

 

[Pete_UK]

Just for completeness, Holly and I exchanged a few more emails (and
files) and I ended up suggesting this amendment to her formula (which
I think was originally suggested by Bob Phillips):

=IF($A3="",
0,SUMIF(D3:IS3,">="&MAX(DATE(YEAR(TODAY()),MONTH(TODAY())-6,DAY(TODA­
Y())),($A3+90)),F3:IU3))

This chooses the later of the two dates given by (today - 6 months)
and (start + 90 days), so that the member of staff has a 90 day grace
period during which any lateness penalties are not counted, and then
penalty points are only counted if they occurred within the last 6
months.

(For the archives).

Pete