So I have two sheets, Expenses and Tax Deductibles. The Expenses sheet has it’s data filled via a Google form where I track date of purchase, the category it fits, whether it’s tax deductible, etc. The “Is this tax deductible” column filled with “Yes” or “No” data. I want to take all the cells that have “Yes” and take all the data from those rows and populate the Tax Deductibles sheet with it.
The Tax Deductibles sheet would sort this data by category and date (Specifically month by month) and display the totals for each category for each month. Dates are UK Format.
This is how the Expenses Sheet looks:
| Date | Item | Deductible? | Category | Amount |
|---|---|---|---|---|
| 24/05/24 | Phone | No | Bills | £10 |
| 25/05/24 | Accountant | Yes | Bills | £200 |
| 26/05/24 | Gas | Yes | Bills | £75 |
| 01/06/24 | Apples | No | Food | £1 |
| 01/06/24 | Lunch | Yes | Food | £15 |
| 13/07/24 | Electric | Yes | Bills | £40 |
And this is how the Tax Deductibles Sheet currently looks:
| Month | Bills | Food | Travel | Other |
|---|---|---|---|---|
| May 24 | £275 | £0 | £0 | £0 |
| June 24 | £0 | £0 | £0 | £0 |
| July 24 | £0 | £0 | £0 | £0 |
| Sep 24 | £0 | £0 | £0 | £0 |
| Oct 24 | £0 | £0 | £0 | £0 |
But this is how Tax Deductibles should look:
| Month | Bills | Food | Travel | Other |
|---|---|---|---|---|
| May 24 | £275 | £0 | £0 | £0 |
| June 24 | £0 | £15 | £0 | £0 |
| July 24 | £40 | £0 | £0 | £0 |
| Sep 24 | £0 | £0 | £0 | £0 |
| Oct 24 | £0 | £0 | £0 | £0 |
Here’s a copy of the sheet I’m using: https://docs.google.com/spreadsheets/d/1Y079euBk63RYcgFOPjBFwMlPi53SUAIgZ-h69_o9Hsc/edit?usp=sharing
The formula I’m currently using in the Tax Deductibles sheet is as follows:
=sumifs(Expenses!$F:$F,Expenses!$E:$E,$B1,Expenses!$D:$D,"Yes",index(eomonth(Expenses!$B:$B,)),eomonth(A$2,))
So this works for the first cell (B2 in the Google sheet), but it doesn’t seem to work when expanded across other cells in the same column or row (Or any others for that matter).
The numbers in the tables here are slightly different than the data in the actual sheet I’ve linked but the point is still the same.
For reference I’ve added some images of the actual tables so you can see how they actually look:
Tax Deductibles Sheet
Expenses Sheet
Any help would greatly appreciated as this has been stumping me for a while. Thanks!