Hi

I have two columns, A containing dates from January 1 to December 31,
and B containing values entered against the dates.

Without creating range names, what's the shortest formula to:-

1. sum the values between any two given dates (contained in say cells
C1 and C2)

2. the total for any given month nominated in say C3

Thanks for any ideas


--
Brisbane Rob
------------------------------------------------------------------------
Brisbane Rob's Profile: http://www.excelforum.com/member.php...o&userid=25096
View this thread: http://www.excelforum.com/showthread...hreadid=521866

=SUMPRODUCT(--(A1:A100=C1),--(A1:A100<=C2),B1:B100)

=SUMPRODUCT(--(MONTH(A1:A100)=C3),B1:B100)

this assumes C3 will hold the Month NUMBER.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Brisbane Rob"
wrote in message
news:Brisbane.Rob.24miky_1142279102.0707@excelforu m-nospam.com...

Hi

I have two columns, A containing dates from January 1 to December 31,
and B containing values entered against the dates.

Without creating range names, what's the shortest formula to:-

1. sum the values between any two given dates (contained in say cells
C1 and C2)

2. the total for any given month nominated in say C3

Thanks for any ideas


--
Brisbane Rob
------------------------------------------------------------------------
Brisbane Rob's Profile:
http://www.excelforum.com/member.php...o&userid=25096
View this thread: http://www.excelforum.com/showthread...hreadid=521866

With your dates in A4:A368, and values in B4:B368, I got results for
question 1 with this formula:
=SUMPRODUCT(--(C1<=A4:A368),--(C2=A4:A368),B4:B368)

With a date in C3 formatted to show Month-Year, I got results for
question 2 with this formula:
=SUMPRODUCT(--(MONTH(C3)=MONTH(A4:A368)),B4:B368)

Again, I am indebted to you guys.

But can someone explain what the double dash ( -- ) is all about?


--
Brisbane Rob
------------------------------------------------------------------------
Brisbane Rob's Profile: http://www.excelforum.com/member.php...o&userid=25096
View this thread: http://www.excelforum.com/showthread...hreadid=521866

Is this shorter? :)

=SUMIF(A:A,"=C1",B:B)-SUMIF(A:A,"C2",B:B)

The -- coerces the TRUE/FALSE arrays produced by something like
(A1:A100=C1) to 1/0 values that SUMPRODUCT can multiply and sum, you
could do the same with +0 or *1, e.g.

=SUMPRODUCT((A1:A100=C1)+0,(A1:A100<=C2)*1,B1:B10 0)

you don't have to coerce the B1:B100 array because this is numeric


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=521866

Ah ha! Thanks for the explanation.


--
Brisbane Rob
------------------------------------------------------------------------
Brisbane Rob's Profile: http://www.excelforum.com/member.php...o&userid=25096
View this thread: http://www.excelforum.com/showthread...hreadid=521866