Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing related cell values and returning total to summary
Firstly, sorry if the subject makes no sense or doesn't properly relate to my
question, I couldn't think of a way to word it in less than 70 characters. I am compiling a budget spreadsheet to keep track of expenses, within it I have two sheets, a summary giving monthly totals and a detailled sheet containing each expense/income. I am trying to write a function that will be able to take all occurrences of an expense, sum the total value of those expenses and then output the total to the summary sheet for example: SUMMARY SHEET [B1]JAN [C1]FEB (etc) [A2]Rent 500 ... [A3]Shopping (25+15+40) [A4]Gas (10+10+17) DETAILED SHEET [A1]DATE [B1]EXPENSE TYPE(from drop down list) [C1]VALUE [A2]01/02/07 Rent 500 [A3]02/02/07 Shopping 15 [A4]02/02/07 Gas 17 [A5]05/02/07 Shopping 40 [A6]20/02/07 Gas 10 [A7]28/02/07 Gas 10 [A8]03/03/07 Shopping 25 So basically I want Exel to take the three instances of Gas in DETAILED SHEET, Cells [B4], [B6], [B7] and sum their values ([C4]+[C6]+[C7]) and put that total in SUMMARY SHEET Cell [B4]. I hope this is clear enough Can anyone help on this? I'm too much of a novice to think of a way to do this. I thought of an IF/THEN function but couldn't figure out a way for it to accept multiple instances of a true result, without overwriting the value in the summary sheet cell. Thanks in advance |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing related cell values and returning total to summary
Try this formula in cell B2 of your Summary Sheet.
=SUMPRODUCT(--(TEXT('DETAILED SHEET'!$A2:$A10,"mmm")=B$1),--('DETAILED SHEET'!$B2:$B10=$A2),('DETAILED SHEET'!$C2:$C10)) This formula can then be copied to all cells within your Summary Sheet, and should pull the correct amounts based on both row and column headings. NOTE, in your example, you were referring to February Dates on your Detailed Sheet and applying them to the JAN column of your Summary Sheet. I assumed this was a typo. HTH, Elkar "gt_initial" wrote: Firstly, sorry if the subject makes no sense or doesn't properly relate to my question, I couldn't think of a way to word it in less than 70 characters. I am compiling a budget spreadsheet to keep track of expenses, within it I have two sheets, a summary giving monthly totals and a detailled sheet containing each expense/income. I am trying to write a function that will be able to take all occurrences of an expense, sum the total value of those expenses and then output the total to the summary sheet for example: SUMMARY SHEET [B1]JAN [C1]FEB (etc) [A2]Rent 500 ... [A3]Shopping (25+15+40) [A4]Gas (10+10+17) DETAILED SHEET [A1]DATE [B1]EXPENSE TYPE(from drop down list) [C1]VALUE [A2]01/02/07 Rent 500 [A3]02/02/07 Shopping 15 [A4]02/02/07 Gas 17 [A5]05/02/07 Shopping 40 [A6]20/02/07 Gas 10 [A7]28/02/07 Gas 10 [A8]03/03/07 Shopping 25 So basically I want Exel to take the three instances of Gas in DETAILED SHEET, Cells [B4], [B6], [B7] and sum their values ([C4]+[C6]+[C7]) and put that total in SUMMARY SHEET Cell [B4]. I hope this is clear enough Can anyone help on this? I'm too much of a novice to think of a way to do this. I thought of an IF/THEN function but couldn't figure out a way for it to accept multiple instances of a true result, without overwriting the value in the summary sheet cell. Thanks in advance |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing related cell values and returning total to summary
Yes, that was a typo.
Thanks very much for your help, the formula is just what I needed. "Elkar" wrote: Try this formula in cell B2 of your Summary Sheet. =SUMPRODUCT(--(TEXT('DETAILED SHEET'!$A2:$A10,"mmm")=B$1),--('DETAILED SHEET'!$B2:$B10=$A2),('DETAILED SHEET'!$C2:$C10)) This formula can then be copied to all cells within your Summary Sheet, and should pull the correct amounts based on both row and column headings. NOTE, in your example, you were referring to February Dates on your Detailed Sheet and applying them to the JAN column of your Summary Sheet. I assumed this was a typo. HTH, Elkar "gt_initial" wrote: Firstly, sorry if the subject makes no sense or doesn't properly relate to my question, I couldn't think of a way to word it in less than 70 characters. I am compiling a budget spreadsheet to keep track of expenses, within it I have two sheets, a summary giving monthly totals and a detailled sheet containing each expense/income. I am trying to write a function that will be able to take all occurrences of an expense, sum the total value of those expenses and then output the total to the summary sheet for example: SUMMARY SHEET [B1]JAN [C1]FEB (etc) [A2]Rent 500 ... [A3]Shopping (25+15+40) [A4]Gas (10+10+17) DETAILED SHEET [A1]DATE [B1]EXPENSE TYPE(from drop down list) [C1]VALUE [A2]01/02/07 Rent 500 [A3]02/02/07 Shopping 15 [A4]02/02/07 Gas 17 [A5]05/02/07 Shopping 40 [A6]20/02/07 Gas 10 [A7]28/02/07 Gas 10 [A8]03/03/07 Shopping 25 So basically I want Exel to take the three instances of Gas in DETAILED SHEET, Cells [B4], [B6], [B7] and sum their values ([C4]+[C6]+[C7]) and put that total in SUMMARY SHEET Cell [B4]. I hope this is clear enough Can anyone help on this? I'm too much of a novice to think of a way to do this. I thought of an IF/THEN function but couldn't figure out a way for it to accept multiple instances of a true result, without overwriting the value in the summary sheet cell. Thanks in advance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
looking up a value between two numbers and returning a related value | Excel Worksheet Functions | |||
lookup returning incorrect cell values | Excel Worksheet Functions | |||
Summing values incolumns from row cell L7... | Excel Worksheet Functions | |||
summing values for the same cell in 36 different worksheets | Excel Worksheet Functions | |||
Summing Related Values | Excel Discussion (Misc queries) |