Sum entire column from other sheets

[ddunham]

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

 

[Domenic]

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]

Hi Darren,

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

=SUM(Sheet2:Sheet3!B:B)

HTH

Robert

 

[ddunham]

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]

Hi Darren,

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

=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]

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]

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:

=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

 

[Domenic]

So my final formula is:

=SUM(INDEX(INDIRECT(CONCATENATE($A3,"!$A:$F")),0,MATCH(B$2,INDIRECT(CONCATENATE($A3,"!$A$1:$F$1")),0)))

Or...

=SUM(INDEX(INDIRECT("'"&$A3&"'!A:F"),0,MATCH(B$2,INDIRECT("'"&$A3&"'!A1:F1"),0)))

Thanks!

You're very welcome!