How to Get the Sum Total of a Category if Data is Split Between Multiple Adjacent Tables

Question

I have attached a sample of the format the data I am working with is in. The actual data set has many more columns. So I am looking for a single formula that will get the totals for a category from the whole table. As you can see in the photo we have "Test 2" in columns B and D with values of 1 and 9 respectively. That is a total of 10. Is there a singular formula that would return 10? Thank you.

Answer 1

try:

=QUERY({FLATTEN(FILTER(B2:G10, MOD(COLUMN(B:G),   2)=0)), 
        FLATTEN(FILTER(B2:G10, MOD(COLUMN(B:G)-1, 2)=0))}, 
 "select Col1,sum(Col2) 
  where Col1 is not null 
  group by Col1 
  label Col1'Name',sum(Col2)'Totals'")

---

for unknown number of columns try:

=ARRAYFORMULA(QUERY(SPLIT(FLATTEN(
 FILTER(B2:1000, MOD(COLUMN(B2:2),   2)=0)&"×"& 
 FILTER(B2:1000, MOD(COLUMN(B2:2)-1, 2)=0)), "×"), 
 "select Col1,sum(Col2) 
  where Col2 is not null 
  group by Col1 
  label Col1'Name',sum(Col2)'Totals'"))