Sum all values from field next to specific categories (Dropdown) in Excel

0
votes

I am doing an excel sheet for my finances. In an Excel document, I have these separate sheets:

  • 2021 (Overview of all earnings and expenses)
  • January
  • ...
  • December
  • Settings (Dropdown-menu for categories)

In all sheets from January to December have my expenses. The next is an example for January:

| Total |  74,97 |
| -------- | --- |

| Item     |    Date    | Paid with |   Categorie  | Amount |
| -------- | ---------- | --------- | ------------ | ------ |
| Book     | 21.01.2021 |   PayPal  | Education [v]|  14,99 |
| Book     | 23.01.2021 | CreditCard| Education [v]|  9,99  |
| T-Shirt  | 25.01.2021 | DebitCard |  Fashion  [v]|  49,99 |

and this an example for Settings-sheet:

|  x  |
| --- |
| Education |
| Fashion |
| TravelExpenses |
| Groceries |
| Taxes |

etc...

From these sheets, I'm using for the Dropdown menu for each entry in the specific month:

|  x  |
| ------------ |
| Education [v]|
| Fashion   [v]|
| Taxes     [v]|

Overview (2021) sheet looks like:

| Categorie  |   Januar  |   Februar  |   ...     |   December  |
| --------   | --------- | ---------- | --------- | ----------- |
| Fashion    | 49,99     |            |           |             |
| Education  | 24,98     |            |           |             |

So my question is, how to pass the value from each months next field near specific category. Can I sum all values from each month? For example: =SUM(Januar!$A$2)

But how to sum for each categorie seperatly? I guess add extra table in each month sheet, and then pass the value to the overview sheet. But how can I search for values from each category and sum all values from near lying fields?

Any ideas?

Thanks!

1
excelexcel-formula

1 Answers

0
votes

Not sure if I understand your question correct, but following the logic in the description I think this is what you want: =SUMIF(INDIRECT("'"&B$1&"'!D1:D5"),$A2&"*",INDIRECT("'"&B$1&"'!E1:E5"))

It's a SUMIF to the sheet with the name of the month in row 1 of the overview sheet that sums all values from column E in that sheet where the value in column D starts with the value in column A in your overview sheet.