Summarizing Expense Sheets

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
 
N

Norman Harker

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
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
T

Tumbleweed

"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
 
Top