Summing related cell values and returning total to summary

[Guest]

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

 

[Guest]

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

 

[Guest]

Yes, that was a typo.

Thanks very much for your help, the formula is just what I needed.

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

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