Sum If range of dates date range, sum totals
 

I am trying to figure out how to sum a function if it apears between a set
given dates. my sheet looks something like this.
YTD Bid Summary
A B
1 10/12/08 25,000
2 10/28/08 75,000
3 11/25/08 50,000
4 Total 150,000

YTD Monthly Bid Summary
A B
1 October Total of all October
2 November Total of all November
3 December Total of all December
4 Total

I do not have my columns or rows named I tried using the following equation
=SUMIF('YTD Bid Summary'!E6:E34,"=10/31/08",'YTD Bid
Summary'!J6:L34)+SUMIF('YTD Bid Summary'!E,"10/01/08",'YTD Bid
Summary'!J6:L38)

It just says #NAME?

Sum If range of dates date range, sum totals
 

=SUMIF('YTD Bid Summary'!E6:E34,"="&DATE(2008,10,01),'YTD Bid
Summary'!J6:L34)
-SUMIF('YTD Bid Summary'!E6:E34,"="&DATE(2008,11,01),'YTD Bid
Summary'!J6:L34)

make sure your ranges are the same size.

--
__________________________________
HTH

Bob

"Stilmovin" wrote in message
...
I am trying to figure out how to sum a function if it apears between a set
given dates. my sheet looks something like this.
YTD Bid Summary
A B
1 10/12/08 25,000
2 10/28/08 75,000
3 11/25/08 50,000
4 Total 150,000

YTD Monthly Bid Summary
A B
1 October Total of all October
2 November Total of all November
3 December Total of all December
4 Total

I do not have my columns or rows named I tried using the following
equation
=SUMIF('YTD Bid Summary'!E6:E34,"=10/31/08",'YTD Bid
Summary'!J6:L34)+SUMIF('YTD Bid Summary'!E,"10/01/08",'YTD Bid
Summary'!J6:L38)

It just says #NAME?

Sum If range of dates date range, sum totals
 

Hi,

You can use the SUMPRODUCT() formula he

=sumproduct((A1:A310/1/2008)*(A1:A3<10/31/2008),B1:B3). For other months,
change the month in the date.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Stilmovin" wrote in message
...
I am trying to figure out how to sum a function if it apears between a set
given dates. my sheet looks something like this.
YTD Bid Summary
A B
1 10/12/08 25,000
2 10/28/08 75,000
3 11/25/08 50,000
4 Total 150,000

YTD Monthly Bid Summary
A B
1 October Total of all October
2 November Total of all November
3 December Total of all December
4 Total

I do not have my columns or rows named I tried using the following
equation
=SUMIF('YTD Bid Summary'!E6:E34,"=10/31/08",'YTD Bid
Summary'!J6:L34)+SUMIF('YTD Bid Summary'!E,"10/01/08",'YTD Bid
Summary'!J6:L38)

It just says #NAME?

Sum If range of dates date range, sum totals
 

Your formula fails, Ashish, because 10/1/2008 to Excel is 10 divided by 1
divided by 2008. You need to use either Date(2008,10,1) or --"10/2/2008"

Regards,
Fred.

"Ashish Mathur" wrote in message
...
Hi,

You can use the SUMPRODUCT() formula he

=sumproduct((A1:A310/1/2008)*(A1:A3<10/31/2008),B1:B3). For other
months, change the month in the date.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Stilmovin" wrote in message
...
I am trying to figure out how to sum a function if it apears between a
set
given dates. my sheet looks something like this.
YTD Bid Summary
A B
1 10/12/08 25,000
2 10/28/08 75,000
3 11/25/08 50,000
4 Total 150,000

YTD Monthly Bid Summary
A B
1 October Total of all October
2 November Total of all November
3 December Total of all December
4 Total

I do not have my columns or rows named I tried using the following
equation
=SUMIF('YTD Bid Summary'!E6:E34,"=10/31/08",'YTD Bid
Summary'!J6:L34)+SUMIF('YTD Bid Summary'!E,"10/01/08",'YTD Bid
Summary'!J6:L38)

It just says #NAME?

Sum If range of dates date range, sum totals
 

"Fred Smith" wrote in message
...
Your formula fails, Ashish, because 10/1/2008 to Excel is 10 divided by 1
divided by 2008. You need to use either Date(2008,10,1) or --"10/2/2008"

If using that format, use a non-ambiguous date format else it fails on some
systems, that is

--"2008-10/02"

Sum If range of dates date range, sum totals
 

Good point, Bob.

Regards,
Fred.

"Bob Phillips" wrote in message
...

"Fred Smith" wrote in message
...
Your formula fails, Ashish, because 10/1/2008 to Excel is 10 divided by 1
divided by 2008. You need to use either Date(2008,10,1) or --"10/2/2008"

If using that format, use a non-ambiguous date format else it fails on
some systems, that is

--"2008-10/02"

Sum If range of dates date range, sum totals
 

Thanks Ashish Mathur, Fred Smith, Bob Phillips

This is the equation that i came up with that made it work
=SUMPRODUCT(('YTD Bid Summary'!E6:E32DATE(2008,10,1))*('YTD Bid
Summary'!E6:E32<DATE(2008,10,31)),'YTD Bid Summary'!J6:J32)

I tried to use Bob's equation for the date but that didn't work. Using the
Date function it pulled out the right numbers. Thanks for your help i will be
comming back for any other problems that i have.


"Fred Smith" wrote:

Good point, Bob.

Regards,
Fred.

"Bob Phillips" wrote in message
...

"Fred Smith" wrote in message
...
Your formula fails, Ashish, because 10/1/2008 to Excel is 10 divided by 1
divided by 2008. You need to use either Date(2008,10,1) or --"10/2/2008"

If using that format, use a non-ambiguous date format else it fails on
some systems, that is

--"2008-10/02"

Sum If range of dates date range, sum totals
 

This is how it would be

=SUMPRODUCT(('YTD Bid Summary'!E6:E32--"2008-10-01")
*('YTD Bid Summary'!E6:E32<--"2008-10-31"),'YTD Bid Summary'!J6:J32)

--
__________________________________
HTH

Bob

"Stilmovin" wrote in message
...
Thanks Ashish Mathur, Fred Smith, Bob Phillips

This is the equation that i came up with that made it work
=SUMPRODUCT(('YTD Bid Summary'!E6:E32DATE(2008,10,1))*('YTD Bid
Summary'!E6:E32<DATE(2008,10,31)),'YTD Bid Summary'!J6:J32)

I tried to use Bob's equation for the date but that didn't work. Using the
Date function it pulled out the right numbers. Thanks for your help i will
be
comming back for any other problems that i have.


"Fred Smith" wrote:

Good point, Bob.

Regards,
Fred.

"Bob Phillips" wrote in message
...

"Fred Smith" wrote in message
...
Your formula fails, Ashish, because 10/1/2008 to Excel is 10 divided
by 1
divided by 2008. You need to use either Date(2008,10,1)
or --"10/2/2008"

If using that format, use a non-ambiguous date format else it fails on
some systems, that is

--"2008-10/02"