SUMIFS over multi sheets

[BooInc]

I'm learning to use formulas in excel I need your help with this SUMIFS one.

So when you see the sheet, I want to type in the data on the "Item" sheet and have it automatically added into the "Year" sheet. I want it so it looks up the date, category then adds the cost into its category on the "Year" sheet.

I've tried this =SUM(SUMIFS(Item!C2:C5,Item!B2:B5,{"Food"},Item!A2:A5,{"July 2020"})) It keeps on returning zero.

Can anyone help with the formula?

Thank you

[oeldere]

I would solve this with pivot table.

[BooInc]

I would solve this with pivot table.

Thank you but it wasn't what I was after.
I want it so the "Item" sheet automatically gets added and placed on the "Year" sheet under the right date and category.

[oeldere]

Did you noticed the pivot table does that requirement?

Edit

I edit the excel example with more data.

Edit2

the month and year don't match the data in in the imput sheet.

[davsth]

Perhaps
=SUMIFS(Item!$C$2:$C$10,Item!$A$2:$A$10,">="&Year!$A4,Item!$A$2:$A$10,"<="&EOMONTH(Year!$A4,0),Item!$B$2:$B$10,Year!B$2)

but your item is in 2020 so does not appear anyway!

[BooInc]

Perhaps
=SUMIFS(Item!$C$2:$C$10,Item!$A$2:$A$10,">="&Year!$A4,Item!$A$2:$A$10,"<="&EOMONTH(Year!$A4,0),Item!$B$2:$B$10,Year!B$2)

but your item is in 2020 so does not appear anyway!

I want it so I input the data on the "Item" sheet and it goes to the "Year" sheet.
Eg: 10/07/20 - Food - $40.00 (In Item sheet)
then I go to the year sheet and under July 20 Food there would be the $40.00 there.
Make sense?

[oeldere]

@BooInc

There is no "year sheet and under July 20" in your file.




Edit

Change Year!A4 in july-20

Then use the formula of davsth in #5.

The formula works as expected. I tested it.

[BooInc]

I've fixed it up changing the date format.

Thank you oeldere and davsth for the help.