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

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?

Answers

  • ChristianFinke
    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.

    CHRISTIAN FINKE

    cfinke@digitalradius.com

    Schedule a Meeting

  • jjg279
    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
    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.

    Michelle Choate

    michelle.choate@outlook.com

    Always happy to walk through any project you need help with!