add rows figures in a column if selected by a variable

[Paul]

Hi guys,
(Excel 2003)
Problem I have is this;
In a workbook called banking.xls, I have a worksheet called 2008. In the
worksheet there are a number of columns for monies paid in by various methods
(Chq, card, cash.....etc) which are totalled each month. These payments are
bound for 1 of 2 ledgers, at present in another column I enter via a drop
down box either "A" or "BL" to designate the ledger it belongs to. At the end
of each month I need to know how much belongs to each account. So in essence
something that will take a range of cells eg. c10 to c100 and d10 to d100 e10
to e100 look across to column J where each row is marked either "A" or "BL"
via a drop down box and provide an answer how much for "A" and how much for
"BL". However, I would need to change the range each month for columns cdef
and J
Appreciate your help in advance
Regards

 

[Roger Govier]

Hi Paul

On another Sheet, set up as follows
In B2 Enter A and in C2 enter BL and in A3 Enter TOTAL
Put your start date in B1 and your End date in B1

In B3
=SUMPRODUCT((Sheet1!$A$2:$A$1000>=$B$1)*(Sheet1!$A$2:$A$1000<=$C$1)*
(Sheet1!$J$2:$J$1000=B$2)*Sheet1!$C$2:$F$1000)

Copy across to C3
Change the values in B1 and C1 each month
Change the range to suit, but ensure all ranges are of equal length.