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