Sum entire column from other sheets

ddunham

New Member
Joined
May 12, 2005
Messages
9
I have a summary page that I'd like to sum columns from other sheets. They all have simple names in row1 and a variable number of data rows.

Summing in a literal function like sum(sheet!C:C) is pretty simple, but I can't see any easy way to sum a column based on which column has a matching value in row1.

It's easy enough to use MATCH to find which column number it is, but I can't see how to use that to create an array reference for the entire column. I tried using Offset like: OFFSET(sheet!A:F,1,3,,1) to return an array for column C, but it doesn't seem to be valid.

If I wanted a row, I think I could use (too many) concatenates and indirects to create the correct "sheet!x:x" reference. But turning the number into the correct letter seems more difficult, and I just think there's an easier way. Is there?

I looked at DSUM, but I have no criteria. I just want to sum the entire column.

Thanks!
--
Darren
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,406
Is this what you mean?

=SUM(INDEX(Sheet2!$A$2:$F$100,0,MATCH(A2,Sheet2!$A$1:$F$1,0)))

The formula will search A1:F1 on Sheet2 for the lookup value in A2 and sum the corresponding matching column.
 

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,714
Hi Darren,

Perhaps a "3D" (or cubed) formula might do the trick. The following will sum Column B for Sheets 2 and 3 (inclusive):

Code:
=SUM(Sheet2:Sheet3!B:B)

HTH

Robert
 

ddunham

New Member
Joined
May 12, 2005
Messages
9
Is this what you mean?

=SUM(INDEX(Sheet2!$A$2:$F$100,0,MATCH(A2,Sheet2!$A$1:$F$1,0)))

The formula will search A1:F1 on Sheet2 for the lookup value in A2 and sum the corresponding matching column.

It does. I've certainly thought of that and similar, but I was hoping to avoid hard-coding a maximum row number. For this particular problem, I'll probably never have more than a few thousand rows, but was looking for the general solution.
--
Darren
 

ddunham

New Member
Joined
May 12, 2005
Messages
9

ADVERTISEMENT

Hi Darren,

Perhaps a "3D" (or cubed) formula might do the trick. The following will sum Column B for Sheets 2 and 3 (inclusive):

Code:
=SUM(Sheet2:Sheet3!B:B)

Saw that, but I don't know that my columns will line up perfectly across all the sheets. I know that the columns will all be properly labeled in row1.

Also, I don't really want to sum across all the sheets. I want my summary sheet to have sums for individual sheets in different cells.
--
Darren
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,406
It does. I've certainly thought of that and similar, but I was hoping to avoid hard-coding a maximum row number. For this particular problem, I'll probably never have more than a few thousand rows, but was looking for the general solution.
--
Darren

If you're using Excel 2003, convert your data into a list...

Data > List > Create List

The range will automatically adjust as data is added/removed. If you have a prior version of Excel, use a dynamic named range. Or, since SUM ignores text values, if A1:F1 on Sheet2 contains text values, you can replace...

Sheet2!$A$2:$F$100

with

Sheet2!$A:$F
 

ddunham

New Member
Joined
May 12, 2005
Messages
9
If you're using Excel 2003, convert your data into a list...

Data > List > Create List

Yes, but I have to do that work manually on new sheets, yes? I was trying to avoid touching the incoming sheets.

Or, since SUM ignores text values, if A1:F1 on Sheet2 contains text values, you can replace...

Sheet2!$A$2:$F$100

with

Sheet2!$A:$F

That's it. I was trying to do that with offset, but it didn't like when I removed the row references.

So my final formula is:
Code:
=SUM(INDEX(INDIRECT(CONCATENATE($A3,"!$A:$F")),0,MATCH(B$2,INDIRECT(CONCATENATE($A3,"!$A$1:$F$1")),0)))

I can put sheet names in Column A, Sheet column names in Row 2, and the column sums appear in the intersection. The above formula is in B3.

Thanks!
--
Darren
 

Watch MrExcel Video

Forum statistics

Threads
1,122,822
Messages
5,598,307
Members
414,224
Latest member
Crazy_FC

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top