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?

Find more posts tagged with

Accepted answers

All comments

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.