Using a cell to reference and sum a column on another sheet

jjg279

I am trying to get the sum of an entire column where the column name (or the first cell in the column) is equal a cell on another sheet.

My main sheet looks like this:

And I am trying to sum each of these columns from another sheet to the first where the name matches the cell in the primary column:

Is there a way to either INDEX/MATCH/SUMIF using criteria for a column or use the cell as the "name" of my column reference for the second sheet?

ChristianFinke

jjg279

To sum a column in another sheet based on a matching name in your primary sheet, use a helper row on the second sheet. Add column names as text in the first row of the second sheet. Then, on the primary sheet, use a cross-sheet SUMIF formula:

I would try the below formula
=SUMIF({Helper Row in Second Sheet}, [Primary Column]@row, {Column Range in Second Sheet})

This matches the name in the primary sheet to the helper row and sums the corresponding column. Repeat for other rows as needed.

jjg279

I am getting an #INCORRECT ARGUEMENT SET error, here are the ranges I am working with.

I know I can just select each column individually on the second sheet, but I didn't want to "hard code" the ranges in in case the ranges need updated, and because I have about 40 columns to sum.

Michelle Choate 2

#INCORRECT ARGUMENT SET means you have one too many arguments in a function. In other words, one of your IF statements has too many options in it.

Also - as best practice, try to name your ranges so that way when you have to go back and look at your formulas a year from now, you know what it is you are actually referencing. Column names usually help. You can set up a bunch of references all at once using the reference naming tool inside Smartsheet. That should help with your 40 columns.