Summarizing Expense Sheets

  • Thread starter Thread starter msherman83
  • Start date Start date
M

msherman83

I have an excel worksheet to keep track of 5 different types of expense
that occur each month. They are listed by the date they occur and I a
given a total for all expenses. However, I need to know over the mont
how much was spent for each type of expense.

Is there a formula that can be used to get the total for each type o
expense?

Thanks!

Mik
 
Hi Mike!

A formula approach will usually involve SUMPRODUCT.

eg.

=SUMPRODUCT(--(TEXT($A$1:$A$100,"mmm")="Mar"),--(YEAR($A$1:$A$100)=2004),--(
$B$1:$B$100="Gas"),--($C$1:$C$100))

Sums amounts in column C spent on one item (Gas) in column B during March of
2004 where the dates are in column A.

But I really think that you will be much better off looking at Pivot Tables
which are designed precisely for this type of task.
--
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
"SUMIF" should do the trick for you. Following is an example of the expenses
incurred during a snowbird's relaxing month in the southern sun.

A C D
1/12/2004 Hot Dogs $3.59
1/13/2004 Beer $6.89
1/14/2004 Minnows $2.54
1/15/2004 Gas $10.35
1/16/2004 Boat Rent $20.00
1/16/2004 Hot Dogs $3.59
1/17/2004 Beer $6.15
1/17/2004 Minnows $5.08
1/17/2004 Hot Dogs $3.59
1/18/2004 Beer $6.79
1/18/2004 Minnows $10.40
1/19/2004 Boat Rent $20.00
1/20/2004 Gas $10.79
1/21/2004 Gas $11.50
1/21/2004 Beer $7.49
1/22/2004 Minnows $2.54
1/23/2004 Minnows $5.08
1/24/2004 Hot Dogs $7.21
1/25/2004 Beer $7.29
1/25/2004 Minnows $2.54
1/25/2004 Boat Rent $20.00
1/25/2004 Gas $10.33
1/26/2004 Beer $8.45
1/27/2004 Minnows $2.59
1/28/2004 Gas $10.32
1/29/2004 Boat Rent $20.00
1/30/2004 Beer $7.34
Month Total $232.44

"=SUMIF(C2:C28,"Hot Dogs",D2:D28)" $17.98
"=SUMIF(C2:C28,"Beer",D2:D28)" $50.40
"=SUMIF(C2:C28,"Minnows",D2:D28)" $30.77
"=SUMIF(C2:C28,"Gas",D2:D28)" $53.29
"=SUMIF(C2:C28,"Boat Rent",D2:D28)" $80.00
 
Back
Top