Summing values from multiple tables into one total table based on several criteria

Question

Hey,

I have a seemingly easy question but not being able to find the answer yet.

So, let's say I have 32 similar tables with different values in them. 

now, what I want is - create the 33rd table which will sum all the values from 32 tables based on two criteria - a year and a month. 

Month	Value 1	Value 2	Year
Jan	$25	$50	2017

So, the 33rd table will sum up all the values for "Value 1" column from all 32 tables for each month and year.

Now I am using sumifs function. it looks something like this:

=SUMIFS(Table1[value 1],Table1[Month],"Jan",Table1[Year],"2017")+SUMIFS(Table2[value 1],Table2[Month],"Jan",Table2[Year],"2017")+....

it is a good formula to avoid mistakes but it is tiring to use it for 32 tables.

I tried to name all the ranges i need - to avoid typing out sumifs function multiple times - but that function does not work with multiple ranges selection.

I appreciate any suggestion which makes calculation easier.

Answer 1

What about if you were to list the table names in a column, and then build a formula using =INDIRECT to reference the table names in the cells? That way you would only have to write the formula once and copy it down, and then sum the column at the end.

So for example, in A2 put Table1, A3 put Table2, etc down to A33.

Then in B2 put the formula =SUMIFS(INDIRECT(A2&"[Value]"),INDIRECT(A2&"[Month]"),1,INDIRECT(A2&"[Year]"),1)

and copy this down to B33.

Then in B34, add the formula =SUM(B2:B33) to get the total.

See attached image.

Answer 2

Or sorry more precisely with your formula

=SUMIFS(INDIRECT(A2&"[Value 1]"),INDIRECT(A2&"[Month]"),"Jan",INDIRECT(A2&"[Year]"),"2017")

Answer 3

Thank you for your time Murray.

This would work, but I actually simplified examples in my answer.

In reality i have around 8 columns and more than 100 rows for some of the columns for each table.

so there is already a lot space occupied in working sheets and i do not want to add to that))

again, thank you for your help.

for some reason i think there should be an easier way to do that since this operation seems to be very basic, like something many people need, so excel should have it somewhere.

Answer 4

OK. But there is no need to do this on a working sheet - do it on a completely new sheet (which you could hide if you want). The totals can also go somewhere else - they don't need to be at the bottom of the columns. Extending from the original concept, put '[Value 1]' in B1, [Value 2] in C1 etc to account for the additional columns.

Then the formula in B2 can become something like

=SUMIFS(INDIRECT("Sheet1!"&A2&B$1),INDIRECT("Sheet1!"&A2&"[Month]"),"Jan",INDIRECT("Sheet1!"A2&"[Year]"),"2017")

Or if you prefer, just put Sheet1!Table1 in A2 etc.

Then copy down and to the right for the additional columns.

I hear what you say though - it would be nice if you could do something like:

=SUMIFS(Table1:Table32[value1],Table1:Table32[Month],"Jan",Table1:Table32[Year],"2017")