Formula to Sum values in One Column of a multi-column range

Ramzi

Is there a formula (or trick) to use to do the following?

Samuel Mueller

Are you trying to dynamically choose which column to sum?

Ramzi

That's the idea, yes. I was hoping to use the Index function to somehow reach into the range. Looking for ideas.

Samuel Mueller

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.

Samuel Mueller

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.

Ramzi

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!

Samuel Mueller

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.

Ramzi

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.

Samuel Mueller

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

Samuel Mueller

Ehhh, maybe not. I take that back

Ramzi

A couple of hundred rows. Considered that. But not sure it's worth the trouble.

Ramzi

Samuel Mueller

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

Ramzi

Not a bad idea!!

Ramzi

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

Samuel Mueller

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