Sum amounts based on category and date

Question

Level 1

8 points

Sum amounts based on category and date

I have a table (Expenses) with 4 columns: Date, Description, Category, Amount. This table is on sheet "Year transactions". Then I have another table (Summary by categories) on sheet "January budget". Columns of this table are Category, Budget, Reality, Difference.

I need in the column Reality in the "January budget" sheet a sum of all transactions falling into a category defined in the Category column in "January budget" sheet, but only if the Date in Expenses table is within January. I wrote a following function in the cell:

SUMIFS(Year transactions::Expenses::Amount;Year transactions::Expenses::Category;$A2;MONTH(Year transactions::Expenses::Date);1) When there is just one row in the Expenses table, the Reality cell shows no error and displays the correct amount in that row. But when I add another row to the Expenses table, I get an error message "Function SUMIFS requires that all argument ranges are of the same size". (I translated from my language, the exact message in English might be different).

What's happening? Why the function doesn't work with more than one row?

iPad Pro, iPadOS 17

Posted on Jan 3, 2024 7:31 AM

Reply

Answer 1

SGIII

Level 8

37,562 points

Jan 3, 2024 8:54 AM in response to tobytja

It's hard to know exactly what is happening from your description. A screenshot would help.

Meanwhile you should be able to figure out how you can put SUMIFS function to work by having a look at this expanded version of the built-in Personal Budget Template (Dropbox download). Look in cell E2 of the EXPENSES BY CATEGORY AND MONTH table on the first sheet. The formula summarizes values found in the Transactions table on the second sheet.

SG

Reply

Answer 2

tobytja Author

Level 1

8 points

Jan 3, 2024 11:08 AM in response to SGIII

Problem with screen shot would be it would be in a language most people don't understand (only about 10 million of people world-wide). I used English translations of the values. The Expenses table has two rows - header with names of columns and a single data row. I used the template "Personal budget" and expanded it with incomes and sheets with data for every month. Last year I always noted every expense and income twice - once into the yearly summary, once into monthly summary. But that's highly inconvenient, I'd like to read all the data from the "Year transactions" sheet without needing special sheet for every month. The tables I described are those from the bottom table on the budget sheet and the table on the transactions sheet.

Reply

Sum amounts based on category and date

Similar questions