Adopt IF + SUMIF for values related to CATEGORY/DATE filters on different sheets.

Hi everybody,

I'm recently struggling with particular functions (related to category/time filters) of a newly created dashboard for personal budgeting that I would like to release as an Open-Source project. Before explaining the problem, I would like to briefly introduce the structure of the file.

As you can see from the screenshots*, the dashboard is composed of 6 sheets, 4 of them completely automated thanks to the Transactions sheet.

*values reported are for demonstrative purposes.

---DASHBOARD---

• 1) Accounts: shows the amount of money available into every account. Values are auto-updating thanks to a SUMIF that refers to the sheet Transactions:

---GOAL---

sum-values (+/-) if they match the account's name chosen as a value from the test-values column.

---FUNCTION---

SUMIF (test-values); (condition); (sum-values).

---REFERENCES---

SUMIF (Column: Category [Sheets: Transactions]; (Value: Description [Sheets: Accounts]); (Values: In/Out [Sheets: Transactions]).

Screenshot N.1

Screenshot N.2

• 2) Balance: shows the difference between the sum available (on a specific account) at the start, and the end of the same month. Values are referring to the sheet Transactions, which contain a column with function DATE and other three with YYYY, MM, DD.

---GOAL---

It should be the same function adopted for the Accounts sheet, with an extra variable that should work as a monthly-filter.

---FUNCTION---

?

---REFERENCES---

?

Screenshot N.3

Screenshot N.4

• 3) Budget: shows the total amount of expenses for a certain category (i.e. Food) in a specific month. It also allows comparing the expenses with the chosen budget that needs to be inserted manually.

---GOAL---

It should be the same function adopted for the Accounts sheet, with extra variables that should work as a category, and monthly filters.

---FUNCTION---

?

---REFERENCES---

?

Screenshot N.5

Screenshot N.6

Here we go; what I'm looking for help to create the needed functions for the Balance and Budget sheets. I hope that I've expressed well my intentions and that the screenshots could results useful. I'm sorry for my lack of knowledge related to logic/mathematical vocabulary.

Feel free to ask any additional information you may need, I'll be available for any kind of support. As an extra note, all the people who will participate in fixing this dashboard will be credited once it will be published.

Thanks in advance,

Mirko