Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
add rows figures in a column if selected by a variable
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
add rows figures in a column if selected by a variable
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. -- Regards Roger Govier "Paul" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
In VBA, How to Refer to Cell In Specific Column But Selected Rows? | Excel Discussion (Misc queries) | |||
Function to return # of column with min value in selected rows | Excel Worksheet Functions | |||
How to convert FIGURES in one column to WORDS in another column? | Excel Discussion (Misc queries) | |||
Sum cells based on a row variable and seperate column variable | Excel Worksheet Functions | |||
how do I use variable column and rows using other cell reference . | Excel Worksheet Functions |