Isolating months
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 |
Isolating months
=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 |
Isolating months
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) |
Isolating months
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 |
Isolating months
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 |
Isolating months
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 |
All times are GMT +1. The time now is 03:42 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com