Issue with SUMIF function

Occasional Visitor

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

1 Reply

@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.

https://exceljet.net/excel-pivot-tables