Conditional Sum - I suppose??

paulyf · Mar 28th, 2002, 07:07 AM

Hi all,

I have a worksheet with one row per day covering a period of 5 years. The columns are "amounts paid by subscribers" with amounts entered on the day (row) they were paid, i.e. a matrix format. The "user" selects a date using drop down boxes. I have formatted the date to match that in the main worksheet. I would now like to show cumulative payments by any given subscriber at the date selected by the "user". I have tried to weld the SUM function onto LOOKUPs etc but with no success. I do not want to add additional "cumulative payment columns" for each subscriber (then use LOOKUP). How do I make the SUM formula sensitve to a variable date?

Any help greatly appreciated.

Cheers - Paul

Dave Hawley · Mar 28th, 2002, 07:24 AM

Hi Paul

Let's say the dates are in Column "A"
Cell holding the needed date is C1
The values to add are in Column "B"

=SUMIF($A$1:$A$1000,$C$1,$B$1:$B$1000)

Should help

_________________
Kind Regards
Dave Hawley
OzGrid Business Applications
Microsoft Excel/VBA Training

[ This Message was edited by: Dave Hawley on 2002-03-28 06:26 ]

IML · Mar 28th, 2002, 07:25 AM

Paul,
It sound like a sumif statement may meet your needs. The format would be
=SUMIF(A1:A13,">"&C1,B1:B13)

where A1:A13 is payment dates and B1:B13 are corresponding payments. C1 would be the date your user entered. The would show all payments after this date. To be inclusive of that date simply change it to >=.

good luck

Or what dave said

[ This Message was edited by: IML on 2002-03-28 06:26 ]

[ This Message was edited by: IML on 2002-03-28 06:27 ]

paulyf · Mar 28th, 2002, 07:45 AM

Problem Solved - Many Thanks.

You've no idea the "pain" my ignorance has cuaused me over the last 3 months!!!

Cheers - Paul

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 28th, 2002, 07:07 AM	#1
paulyf New Member Join Date: Mar 2002 Location: Paul Posts: 34	Hi all, I have a worksheet with one row per day covering a period of 5 years. The columns are "amounts paid by subscribers" with amounts entered on the day (row) they were paid, i.e. a matrix format. The "user" selects a date using drop down boxes. I have formatted the date to match that in the main worksheet. I would now like to show cumulative payments by any given subscriber at the date selected by the "user". I have tried to weld the SUM function onto LOOKUPs etc but with no success. I do not want to add additional "cumulative payment columns" for each subscriber (then use LOOKUP). How do I make the SUM formula sensitve to a variable date? Any help greatly appreciated. Cheers - Paul

Mar 28th, 2002, 07:24 AM	#2
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	Hi Paul Let's say the dates are in Column "A" Cell holding the needed date is C1 The values to add are in Column "B" =SUMIF($A$1:$A$1000,$C$1,$B$1:$B$1000) Should help _________________ Kind Regards Dave Hawley OzGrid Business Applications Microsoft Excel/VBA Training [ This Message was edited by: Dave Hawley on 2002-03-28 06:26 ]

Mar 28th, 2002, 07:25 AM	#3
IML MrExcel MVP Join Date: Feb 2002 Location: Denver, CO Posts: 1,744	Paul, It sound like a sumif statement may meet your needs. The format would be =SUMIF(A1:A13,">"&C1,B1:B13) where A1:A13 is payment dates and B1:B13 are corresponding payments. C1 would be the date your user entered. The would show all payments after this date. To be inclusive of that date simply change it to >=. good luck Or what dave said [ This Message was edited by: IML on 2002-03-28 06:26 ] [ This Message was edited by: IML on 2002-03-28 06:27 ]

Mar 28th, 2002, 07:45 AM	#4
paulyf New Member Join Date: Mar 2002 Location: Paul Posts: 34	Problem Solved - Many Thanks. You've no idea the "pain" my ignorance has cuaused me over the last 3 months!!! Cheers - Paul