Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
if and sumif
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, |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
if and sumif
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 " wrote: 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, |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
if and sumif
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. wrote: 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, |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
if and sumif
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 FormatCellsCustommmm This will show just the Month of the transaction Mark your header row and use DataFilterAutofilter 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 -- Regards Roger Govier wrote in message oups.com... 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, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to use SUMIF to return sums between two values located in cells | Excel Worksheet Functions | |||
Sumif of Sumif perhaps? | Excel Discussion (Misc queries) | |||
SUMIF | Excel Worksheet Functions | |||
SUMIF with Mutiple Ranges & Criteria | Excel Discussion (Misc queries) | |||
SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions |