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
-
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. -
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.
-
#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!
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives