0
1
I have many tables and I want to summarize these table into one table.
As shown in the image I want to summarize the above tables into the one appears at the bottom. Notice that the above tables may contain different columns and rows.
Also I have about 200 rows and cols.
Notice that I don't want to use VBA.
myahia
Posted 2017-03-09T08:50:12.693
Reputation: 3
0
You can use this formula and change refernce according to your Data (in the same sheet or different sheets) In B10 write the following:
=SUMPRODUCT(--($B$1:$F$1=B$9)*($A$2:$A$6=$A10)*$B$2:$F$6)+SUMPRODUCT(--($I$1:$L$1=B$9)*($H$2:$H$5=$A10)*$I$2:$L$5)+SUMPRODUCT(--($O$1:$S$1=B$9)*($N$2:$N$7=$A10)*$O$2:$S$7)
B1:F1 is the header of the first table you can write sheet1!$B$1:$F$1 if in sheet1 (the last column instead of F)
A2:A6 is the header left in the first table also write sheet1!$A$2:$A$6 (the last row instead of 6)
Each sumproduct for each table and you can drag the formula by row and by column
yass
Posted 2017-03-09T08:50:12.693
Reputation: 2 409
Thanks a lot @yass. I have tried your formula with simple tables that I show and it works like a magic. I will try it with more complex data and I hope it will work. Again thanks a lot. – myahia – 2017-03-09T18:11:40.330
Just try to change references according to your real Data – yass – 2017-03-09T18:39:16.830
where are your tables located? In different sheets? Same sheet? Do all have the same columns & rows in the same order? (e.g. is 4th row "c" in each sheet)? – Máté Juhász – 2017-03-09T08:56:26.303
in the same file but in different sheets. As I mentioned the rows and cols are different. For example, the title of cols or rows is starting from a to z but not all of them are included in the tables as shown in the attached image. So the new table that I want to create should include all cols and rows that appear in the original tables but without duplicates. – myahia – 2017-03-09T17:59:18.163