if and sumif

[KurtABeard]

I am putting together a budget spreadsheet and would like to have one
expense tracking spreadsheet.
In my budget sheet I want to run check two pieces of data in the
expense sheet. I want to check the month and the category of an expense
then pull it over into the budget sheet. I am currently using the sumif
function to call by category but I also want to check the month. My
goal is to have a budget tab for each month and one expense tab in
order to do this I need to get the sum of the information based on the
month column and the category column. Ie I need all the food expenses
for July.

Thanks for the help,

 

[Guest]

Perhaps:

=SUMPRODUCT(--(MONTH(A1:A100)=7),--(B1:B100="Food Expenses"),(C1:C100))

where A1:A100 are dates .. selecting month =7 (July)
B1:B100 are the categories
C1 :C100 are value to be summed

HTH

 

[TryfanMan]

Hi,

This is a similar question to one posted a few posts ago. I think the
best tool for the job here is an array formula. This allows you to sum
based on multilpe criteria. An example is:

=sum(if($A$2:$A$200="July",if($B$2:$B$200="Food",$C$2:$C$200)))

Press ctrl & shift when you have entered all the arguments to tell
Excel you
want it to be an array forumla. You will know if it has worked because

it will put braces ( { } ) around the function in the formula bar.

This example assumed your months are in column A, category in column B
and the value you would like to sum in column C.

It would be better to reference your sum criteria to cells instead of
inserting it directly as I have above.

Good luck.

 

[Roger Govier]

Hi Kurt

IMO you would be better off keeping all of your data on one tab, rather
than a separate tab for each month.
Using filters, you can have a quick view of the data for any specific
month very easily, and one set of formulae will bring the summary for
the year to another sheet if required.

Suppose you have columns set out in columns A to D as
Date Item Cost Category

Add a further column E titled Month, and in E2 put the formula =A2 and
Format>Cells>Custom>mmm
This will show just the Month of the transaction
Mark your header row and use Data>Filter>Autofilter and use the Dropdown
on Month to select the month you wish to see.
Select the Category to view all items for that Category for the Month.
If you insert a new row 1, above your headings, enter in C1
=SUBTOTAL(9,C3:C1000)
and you will see the total cost for the filtered set of data.

If you want a summary sheet to show your costs categorised by month, on
Sheet2 set up a table with your categories going down the page starting
from cell A2.
Set up the Months Jan, Feb Mar etc across the page starting in B1
In cell B2 enter
=SUMPRODUCT(--(TEXT(Sheet1!$A$2:$A$1000=$B1),--(Sheet1!$D$2:$D$1000=$A2),$C2:$C1000)
Copy the formula across the page for your 12 months, and down the page
for as many category rows as you have set up.

With all your data on one sheet, you could always use a Pivot Table to
produce your summary.
Take a look at Debra Dalgleish's site for information on how to set up a
pivot table report.
http://peltiertech.com/Excel/Pivots/pivotstart.htm
and
http://www.contextures.com/tiptech.html