I have a table with an 'item' column that contains a string, and several columns for the amount spent on that item in different months of the year, like so:
| Item | Jan | Feb |
|---|---|---|
| First | 10 | 20 |
| Second | 35 | 3 |
| Third | 70 | 17 |
I then have a second table that should total each row, like so:
| Item | Total |
|---|---|
| First | 30 |
| Second | 38 |
| Third | 87 |
However, I cannot get this second table to total them properly. I realize that I could very easily achieve this by making each cell of Table2 =SUM(B2:C2) or something like that and extending it over the other rows. The issue is that 1) I want to use structured references and 2) I don't know how many months I will use this table for, so I want to add on a month as they pass by, and I don't want to adjust the formulas every month. I have several pairs of tables like this.
One idea I had to solve the first issue is this formula:
=SUMIF(Table1[ITEM], [@ITEM], Table1[[Jan]:[Feb]])
The idea being that it sums the row if the Item label is the same. However, when I add the March column I have to adjust the formula, which I don't want.
A similar idea to address the second problem is this formula:
=SUMIF(Table1[ITEM], [@ITEM], Table1)
The idea being that it sums the whole row, ITEM column included (but just takes a string as 0). But this doesn't work, the sum in each row comes to 0 for some reason. I think the string messes with it somehow.
Another idea is to add a 'Total' column to Table1 and keep everything in one table, but I can't sum the whole row without circular references. And again, specifying a column range means adjusting the formula every month.
Best Answer
You can use INDEX to return the full row:
Now as it fills down it will change which row it is returning.
We can change out
ROW($ZZ1)for MATCH and not worry about order:As you can see neither refers to a column range (except the MATCH) allowing the addition of columns in Table1 and it will adjust automatically.