Feb 21 2022 09:49 PM
Hi,
I'm working on a budget report. On one sheet (Sheet 1) I have all the major spending categories in the rows and months on the columns. On another sheet (Sheet 2) I have a break down of all the expenses by date. Each expense category is in a different column.
I want to set up a formula on Sheet 1 so that it can look at a given expense category and sum all the expenses on a given month.
My issue is that on Sheet 1 the column only says January and on Sheet 2 I have the full date ie: 15 January 2022.
I tried using the following: =SUMIF(Sheet 2!A2:A40,'Sheet 1'C5,Sheet 2!B2:B40)
Where A2-40 are the full dates, C5 is the word January, B2-40 are the expenses.
I also tried: =SUMIF(Sheet 2!A2:A40,'Sheet 1'"*"&C5&"*",Sheet 2!B2:B40)
In both cases I got $0 as a result, which is definitely not the right sum.
I would appreciate your help
Feb 21 2022 10:07 PM
@excelnewbie101 Why not collect all expenses in one table with four columns (Date, Description, Amount and Expense Category). So, do NOT use a separate column for each category.
From this table you can create the summary as mentioned in your question (Categories in rows and Months in columns) in a few seconds using a pivot table. The link below is a good place to start learning about such tables.