Trying to collapse a table, what Function should I use?

[Brad E.]

I have a table of data (numbers) which is 70 rows by 21 columns, which I
would like to summarize into a 5 x 3 table. So every 14 rows by 7 columns
sums into a single cell on another worksheet.
My original table would be like
1 2 3 ... 20 21
1
2
....
69
70

and my summary table would be
01-07 08-14 15-21
01-14 A B C
15-28 D E F
29-42 G H I
43-56 J K L
57-70 M N O
where A = sum of all numbers in the intersection of rows 1-14 and columns 1-7
where O = sum of all numbers in the intersection of rows 57-70 and columns
15-21
Of course, my references to rows and columns in my A and O definitions are
my header row and header column, not Excel rows and columns.

I started wit
=SUMIFS(Original!$B$2:$V$71,Original!$B$1:$V$1,">="&Left(B$1,2),Original!$B$1:$V$1,"<="&Right(B$1,2),Original!$A$2:$A$71,">="&Left($A2,2),Original!$A$2:$A$71,"<="&Right($A2,2))
which didn't work, so I tried a SUMPRODUCT formula which I can't get to work
either.

Obviously, I could come up with a formula for each entry (A-O above), but
can anyone help me figure out a formula where I can enter it for the A entry
above and copy to O?
-- TIA, Brad E.

 

[Brad E.]

I kept playing with it and got something to work. I am using a simple SUM
function with an OFFSET, and putting a height and width in the OFFSET
function
=SUM(OFFSET(Original!$A$1,VALUE(LEFT($A2,2)),VALUE(LEFT(B$1,2)),VALUE(RIGHT($A2,2))-VALUE(LEFT($A2,2))+1,VALUE(RIGHT(B$1,2))-VALUE(LEFT(B$1,2))+1))

I will continue checking back to see if anyone can come up with anything
different. Thanks for your time.
-- Brad E.

 

[Amish]

Is there a reason you're not using Excel's group/outline feature?

 

[excelent]

=SUM(OFFSET(Original!$B$2,(ROW(1:1)-1)*14,(COLUMN()-COLUMN($B:$B))*7,14,7))

if u dont insert formula in column B in destination sheet then change
COLUMN($B:$B) to suit

"Brad E." skrev: