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))

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

On Nov 3, 10:20*pm, hmsawyer
wrote:
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(TODA*Y())),F3:IU3)-SUMIF(D3:IS3,"<"&($A3+90),F3:IU3))

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.

"Pete_UK" wrote:

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

On Nov 3, 10:20 pm, hmsawyer
wrote:
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(TODAÂ*Y())),F3:IU3)-SUMIF(D3:IS3,"<"&($A3+90),F3:IU3))

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" wrote:

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))

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.

"Sheeloo" wrote:

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" wrote:

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))

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

"hmsawyer" wrote in message
...
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.

"Pete_UK" wrote:

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

On Nov 3, 10:20 pm, hmsawyer
wrote:
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(TODA*Y())),F3:IU3)-SUMIF(D3:IS3,"<"&($A3+90),F3:IU3))

On Nov 3, 3:18 pm, hmsawyer
wrote:
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.

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" wrote:

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

"hmsawyer" wrote in message
...
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.

"Pete_UK" wrote:

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

On Nov 3, 10:20 pm, hmsawyer
wrote:
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(TODAÂ*Y())),F3:IU3)-SUMIF(D3:IS3,"<"&($A3+90),F3:IU3))

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

"hmsawyer" wrote in message
...
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" wrote:

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

"hmsawyer" wrote in message
...
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.

"Pete_UK" wrote:

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

On Nov 3, 10:20 pm, hmsawyer
wrote:
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(TODA*Y())),F3:IU3)-SUMIF(D3:IS3,"<"&($A3+90),F3:IU3))

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(T ODAY())-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

On Nov 4, 2:21*pm, "Pete_UK" wrote:
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

"hmsawyer" wrote in message

...



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" wrote:

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

"hmsawyer" wrote in message
...
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.

"Pete_UK" wrote:

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

On Nov 3, 10:20 pm, hmsawyer
wrote:
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(TODA* Y())),F3:IU3)-SUMIF(D3:IS3,"<"&($A3+90),F3:IU3))- Hide quoted text -

- Show quoted text -