Issue with SUMIF function

%3CLINGO-SUB%20id%3D%22lingo-sub-3196877%22%20slang%3D%22en-US%22%3EIssue%20with%20SUMIF%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3196877%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20working%20on%20a%20budget%20report.%20On%20one%20sheet%20(Sheet%201)%20I%20have%20all%20the%20major%20spending%20categories%20in%20the%20rows%20and%20months%20on%20the%20columns.%20On%20another%20sheet%20(Sheet%202)%20I%20have%20a%20break%20down%20of%20all%20the%20expenses%20by%20date.%20Each%20expense%20category%20is%20in%20a%20different%20column.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20set%20up%20a%20formula%20on%20Sheet%201%20so%20that%20it%20can%20look%20at%20a%20given%20expense%20category%20and%20sum%20all%20the%20expenses%20on%20a%20given%20month.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20issue%20is%20that%20on%20Sheet%201%20the%20column%20only%20says%20January%20and%20on%20Sheet%202%20I%20have%20the%20full%20date%20ie%3A%2015%20January%202022.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20using%20the%20following%3A%20%3DSUMIF(Sheet%202!A2%3AA40%2C'Sheet%201'C5%2CSheet%202!B2%3AB40)%3C%2FP%3E%3CP%3EWhere%20A2-40%20are%20the%20full%20dates%2C%20C5%20is%20the%20word%20January%2C%20B2-40%20are%20the%20expenses.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20also%20tried%3A%26nbsp%3B%3DSUMIF(Sheet%202!A2%3AA40%2C'Sheet%201'%22*%22%26amp%3BC5%26amp%3B%22*%22%2CSheet%202!B2%3AB40)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20both%20cases%20I%20got%20%240%20as%20a%20result%2C%20which%20is%20definitely%20not%20the%20right%20sum.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20appreciate%20your%20help%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3196877%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3196977%22%20slang%3D%22en-US%22%3ERe%3A%20Issue%20with%20SUMIF%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3196977%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1314193%22%20target%3D%22_blank%22%3E%40excelnewbie101%3C%2FA%3E%26nbsp%3BWhy%20not%20collect%20all%20expenses%20in%20one%20table%20with%20four%20columns%20(Date%2C%20Description%2C%20Amount%20and%20Expense%20Category).%20So%2C%20do%20%3CSTRONG%3ENOT%3C%2FSTRONG%3E%20use%20a%20separate%20column%20for%20each%20category.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFrom%20this%20table%20you%20can%20create%20the%20summary%20as%20mentioned%20in%20your%20question%20(Categories%20in%20rows%20and%20Months%20in%20columns)%20in%20a%20few%20seconds%20using%20a%20pivot%20table.%20The%20link%20below%20is%20a%20good%20place%20to%20start%20learning%20about%20such%20tables.%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fexceljet.net%2Fexcel-pivot-tables%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fexceljet.net%2Fexcel-pivot-tables%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
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