Help with sum of a variable range

[sequence19]

I'm trying to build a simple checkbook program for a friend. I've figured out most of what I want it to do and have been able to program in everything I want except for basically one item. One of the features is a monthly calculator of types. I want it to have inputs for a begin date and end date and then output the beginning balance of that date, the total deposits, the total withdrawals, and the concluding balance. I've already got the beginning/concluding balances, but can't figure out how to do the deposits/withdrawals. I've tried using =sum((lookup([date], [deposits])):(lookup([2nd date], [deposits])) and that obviously didn't work. Any ideas? I've attached the program with some sample data in it so you can better understand what I'm trying to do. Thanks!

[squiggler47]

The formula you want is :-

=SUMIFS($F$5:$F$305,$C$5:$C$305,">="&$L$8,$C$5:$C$305,"<="&$L$9)

=SUMIFS($E$5:$E$305,$C$5:$C$305,">="&$L$8,$C$5:$C$305,"<="&$L$9)

[mubashir aziz]

Write below formula in Cell L12

L12=SUMPRODUCT((C6:C17>=L8)*(C6:C17<=L9)*E6:E17)

[squiggler47]

SUMIFS is more robust and quicker since the OP is using Excel 2007

[squiggler47]

I offered an alternate on your sheet using excel tables, this has the advantage that you can add as many transactions as you want and the formulas will still work, while only searching the minimum number of cells to generate your totals

It also avoids the references to full columns (A:A) as the ranges will expand dynamically!

You can also sort on date, or filter on date ranges, transaction types etc!

To add a transaction, just start typing on the next line, the balance formula will autofill for you!