Formula to Sum values in One Column of a multicolumn range
Is there a formula (or trick) to use to do the following?
Smartsheet Solutions Architect
www.adapture.com
Answers

Are you trying to dynamically choose which column to sum?

That's the idea, yes. I was hoping to use the Index function to somehow reach into the range. Looking for ideas.
Smartsheet Solutions Architect
www.adapture.com

There is no straightforward way to do this.. This is what I came up with, may work after you set it up with helper columns
An index to bring over the proper row value based on the column you want to return, then sum just that 1 column all the time, changing the value in column to return when you want to change columns.

You could put the column to return in the sheet summary, same with the column sum, but you need a way to identify row number, and the index formula in each row. You could make the index formula a column formula if your table is the only values in those columns. There is a way to get row number as a column formula using match on a auto number column.

Thanks very much!
This may work, but I need to do it across 192 columns in a Summary sheet by referencing and summing columns across sheets. Rather than create a cross sheet reference for each column, I was hoping to do one reference for the entire range of columns and then sum based on what column index I need to sum. I'll give this a go in case I can somehow get it to work. I'm also considering using DataMesh since we have the license for it.
Thanks again!
Smartsheet Solutions Architect
www.adapture.com

Yeah give it a try, it may work. You would not be able to create 192 cross sheet references anyway, max is 100, so hopefully this will work.
Datamesh might have a limit too, not sure how many columns you can map  but I don't know that answer.

DataMesh can do 190 columns.
I might also use Join in the source sheet with Char(10) into one cell. Reference that in the destination cell, and then parse it out into 192 columns and use them that way.
Smartsheet Solutions Architect
www.adapture.com

I don't know how many rows of data you have, but maybe you can transpose the columns somehow on the source sheet and do a similar index type formula like above, then just reference two columns

Ehhh, maybe not. I take that back

A couple of hundred rows. Considered that. But not sure it's worth the trouble.
Smartsheet Solutions Architect
www.adapture.com


You could export the sheet in excel, transpose the column names, and then in excel create the proper format for all 192 columns, then just paste the two columns back into Smartsheet. If the columns don't change, this would be pretty easy

Not a bad idea!!
Smartsheet Solutions Architect
www.adapture.com

@Samuel Mueller I think we overthunk it ... after I stepped away for a bit, it dawned on me. Since I need the sum of each column. I created a parent row at the top that summed the children and then indexed into that. Problem solved!
Smartsheet Solutions Architect
www.adapture.com

😂 you know I thought about that, but I didn't say it because I assumed there was a reason you couldn't do that. Haha, glad you figured it out.
Help Article Resources
Categories
Check out the Formula Handbook template!