Sum by date
 

In column B, I am trying to add up the values in columns E, H, K, etc (every
third column), but ONLY if the corresponding date in columns C (for E), F
(for H), I (for K), etc (every third column) is within 6 months from TODAY.
I can move the data around if the values need to be next to the corresponding
dates, but that will move around some list validations I already have set up,
so I'm trying to avoid it if possible.

I think it is a sum if, but I can't get it to work. Any help is
appreciated. Thank you so much!

Sum by date
 

I found this one in an old post and it is almost what I need:

=SUMIF(A1:A100,"=" & DATE(YEAR(TODAY())-1,MONTH(TODAY()),1),B1:B100)

However, I need to use a rolling 6 months and this formula is using an
entire year. How would I change it? Thanks!

"hmsawyer" wrote:

In column B, I am trying to add up the values in columns E, H, K, etc (every
third column), but ONLY if the corresponding date in columns C (for E), F
(for H), I (for K), etc (every third column) is within 6 months from TODAY.
I can move the data around if the values need to be next to the corresponding
dates, but that will move around some list validations I already have set up,
so I'm trying to avoid it if possible.

I think it is a sum if, but I can't get it to work. Any help is
appreciated. Thank you so much!

Sum by date
 

Have you tried
=SUMIF(A1:A100,"=" & DATE(YEAR(TODAY()),MONTH(TODAY())-6,1),B1:B100) ?
--
David Biddulph

"hmsawyer" wrote in message
...
I found this one in an old post and it is almost what I need:

=SUMIF(A1:A100,"=" & DATE(YEAR(TODAY())-1,MONTH(TODAY()),1),B1:B100)

However, I need to use a rolling 6 months and this formula is using an
entire year. How would I change it? Thanks!

"hmsawyer" wrote:

In column B, I am trying to add up the values in columns E, H, K, etc
(every
third column), but ONLY if the corresponding date in columns C (for E), F
(for H), I (for K), etc (every third column) is within 6 months from
TODAY.
I can move the data around if the values need to be next to the
corresponding
dates, but that will move around some list validations I already have set
up,
so I'm trying to avoid it if possible.

I think it is a sum if, but I can't get it to work. Any help is
appreciated. Thank you so much!

Sum by date
 

I notice, however, that the formula you quoted wasn't "using an entire
year", but was going back to the first of the month, so from 17 March 2008
would go to 1 March 2007.
My formula does the same for 6 months so would go to 1 September 2007.
If you actually want to go back 6 months from today (to 17 September 2007),
then change the formula to
=SUMIF(A1:A100,"=" &
DATE(YEAR(TODAY()),MONTH(TODAY())-6,DAY(TODAY())),B1:B100)
--
David Biddulph

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
Have you tried
=SUMIF(A1:A100,"=" & DATE(YEAR(TODAY()),MONTH(TODAY())-6,1),B1:B100) ?
--
David Biddulph

"hmsawyer" wrote in message
...
I found this one in an old post and it is almost what I need:

=SUMIF(A1:A100,"=" & DATE(YEAR(TODAY())-1,MONTH(TODAY()),1),B1:B100)

However, I need to use a rolling 6 months and this formula is using an
entire year. How would I change it? Thanks!

"hmsawyer" wrote:

In column B, I am trying to add up the values in columns E, H, K, etc
(every
third column), but ONLY if the corresponding date in columns C (for E),
F
(for H), I (for K), etc (every third column) is within 6 months from
TODAY.
I can move the data around if the values need to be next to the
corresponding
dates, but that will move around some list validations I already have
set up,
so I'm trying to avoid it if possible.

I think it is a sum if, but I can't get it to work. Any help is
appreciated. Thank you so much!

Sum by date
 

I'm not sure if you only want to check for dates up to 6 months in the
future, or in the past also. Either way, to calculate the date 6 months from
now, paste this into an out-of-the-way cell:

=DATE(IF(MONTH(NOW())6,YEAR(NOW())+1,YEAR(NOW())) ,IF(MONTH(NOW())6,MONTH(NOW())-6,MONTH(NOW())+6),DAY(NOW()))

If you only want to check for future dates, start with a formula like this
in column B:

=SUMPRODUCT(--(C5:C1000<=$A$1),E5:E1000)

In this example, the date 6 months from today is found in cell AA1. Add as
many copies of the SUMPRODUCT formula as necessary to itself, editing the
column references each time. For example:

=SUMPRODUCT(--(C5:C1000<=$A$1),E5:E1000)+SUMPRODUCT(--(F5:F1000<=$A$1),H5:H1000)

Empty cells in the date columns won't cause problems, but text that Excel
can't interpret as a date will. Therefore, the format of your dates might
mean we need to adjust the formula.

If you want to look for past dates within 6 months as well as future dates,
then you can use something like this:

=SUMPRODUCT(--(ABS(C5:C1000-NOW())<=ABS($A$1-NOW())),E5:E1000)

Hope this helps,

Hutch

"hmsawyer" wrote:

In column B, I am trying to add up the values in columns E, H, K, etc (every
third column), but ONLY if the corresponding date in columns C (for E), F
(for H), I (for K), etc (every third column) is within 6 months from TODAY.
I can move the data around if the values need to be next to the corresponding
dates, but that will move around some list validations I already have set up,
so I'm trying to avoid it if possible.

I think it is a sum if, but I can't get it to work. Any help is
appreciated. Thank you so much!

Sum by date
 

That is almost it, but it is calcuating by the month and not the day. For
instance, 8/31/07 is not returning a value (as it shouldn't--it's over 6
months from today), but 9/1/07 is---the oldest date that should return a
value today would be 9/19/07--exactly 6 months ago.

Thanks for the help!

"David Biddulph" wrote:

Have you tried
=SUMIF(A1:A100,"=" & DATE(YEAR(TODAY()),MONTH(TODAY())-6,1),B1:B100) ?
--
David Biddulph

"hmsawyer" wrote in message
...
I found this one in an old post and it is almost what I need:

=SUMIF(A1:A100,"=" & DATE(YEAR(TODAY())-1,MONTH(TODAY()),1),B1:B100)

However, I need to use a rolling 6 months and this formula is using an
entire year. How would I change it? Thanks!

"hmsawyer" wrote:

In column B, I am trying to add up the values in columns E, H, K, etc
(every
third column), but ONLY if the corresponding date in columns C (for E), F
(for H), I (for K), etc (every third column) is within 6 months from
TODAY.
I can move the data around if the values need to be next to the
corresponding
dates, but that will move around some list validations I already have set
up,
so I'm trying to avoid it if possible.

I think it is a sum if, but I can't get it to work. Any help is
appreciated. Thank you so much!

Sum by date
 

I guess we were typing at the same time.

That worked! Thanks so much for the help!

"David Biddulph" wrote:

I notice, however, that the formula you quoted wasn't "using an entire
year", but was going back to the first of the month, so from 17 March 2008
would go to 1 March 2007.
My formula does the same for 6 months so would go to 1 September 2007.
If you actually want to go back 6 months from today (to 17 September 2007),
then change the formula to
=SUMIF(A1:A100,"=" &
DATE(YEAR(TODAY()),MONTH(TODAY())-6,DAY(TODAY())),B1:B100)
--
David Biddulph

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
Have you tried
=SUMIF(A1:A100,"=" & DATE(YEAR(TODAY()),MONTH(TODAY())-6,1),B1:B100) ?
--
David Biddulph

"hmsawyer" wrote in message
...
I found this one in an old post and it is almost what I need:

=SUMIF(A1:A100,"=" & DATE(YEAR(TODAY())-1,MONTH(TODAY()),1),B1:B100)

However, I need to use a rolling 6 months and this formula is using an
entire year. How would I change it? Thanks!

"hmsawyer" wrote:

In column B, I am trying to add up the values in columns E, H, K, etc
(every
third column), but ONLY if the corresponding date in columns C (for E),
F
(for H), I (for K), etc (every third column) is within 6 months from
TODAY.
I can move the data around if the values need to be next to the
corresponding
dates, but that will move around some list validations I already have
set up,
so I'm trying to avoid it if possible.

I think it is a sum if, but I can't get it to work. Any help is
appreciated. Thank you so much!