Examine range, find specific value in a row, then return the value two columns over?
I’m trying to do something with Smartsheet, and getting a little lost in the complexity. What I want to do is examine a range of cells, look in a row, find specific text (entered from a 38 item pick list), then return the value that is 2 columns/cells over from the found text, then SUM all such values found. I think an INDEX/MATCH might accomplish this, but I’m not that fluent in using this feature.
Best Answer
-
You should only have 5 columns based on my suggestion. Activity, Activity Subcategory, Resource, Resource Hours, Resource Cost.
Answers
-
You actually want a SUMIFS.
=SUMIFS({Column You Want To Sum}, {Column Housing Criteria}, @cell = [Picklist Column Name]@row)
-
Here's a screenshot of what I have. What I want to do is, look for every occurrence of, say, "Staff Engineer 2" look 2 rows over to get the cost, then sum all the found cells two rows over in the defined range. In my example below, the value I want to end up with is 616 (197+419)
Help?
-
Paul, thanks. Check out my recent example, and see if what you've suggested will still work. I'll look at your response myself, and see if/how it would work. Thanks again.
-
You would need to set up multiple SUMIFS (one for each grouping of columns) and then add them together.
-
Paul, thanks. Look at my example above, and see if your proposed solution would work. The fact that the "Column I want to Sum" isn't fixed, I'm not sure if it would. I'll play around a bit.
-
I did look at your example above. You would set up a SUMIFS for each of the groupings and then add them together.
-
Ah, so if I have, say, 10 resource choices, I'd need a SUMIFS formula that includes all ten formulas? While I think I can figure that out, there's got to be a better way, no? I mean, what if I had 50 potential resource columns?
-
Ok, thanks. Our responses passed each other in the Ether. I can string together 10 SUMIFS statements for the 10 columns, but I still think there has to be a better way to do this, for larger ranges and more columns.
-
@Paul Newcome , that worked a treat, but the formula required is pretty cumbersome. Thanks!
Anybody with an alternate way to skin this cat?
-
The only way to make the formula more efficient would be to restructure your data. I didn't suggest that simply because your formatting and whatnot seem to be rather deliberate.
-
How would you set up the data? I use a form to populate the data, but it can be formatted as needed to make this work easier
-
I personally would have each resource on its own row. The activity name and sub-category would be repeated multiple times, but having each entry on its own row would make the data much easier to report on.
-
Thanks. I thought about that, and in fact, that's how it started out, but we have almost 50 resource types, which would mean 50 sets of columns, with each set being 4 columns wide (checkbox to select the resource, hourly rate, number of hours, and total cost). I could get that down to 3 if I eliminate the checkbox, and just had people enters hours if appropriate. While the screenshot from above is an intake sheet (not normally viewed by anyone other than me), expanding it to more than 150 columns isn't optimal, either.
-
You should only have 5 columns based on my suggestion. Activity, Activity Subcategory, Resource, Resource Hours, Resource Cost.
-
Hmm, let me think about how that might work. Thanks.
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 67.1K Get Help
- 448 Global Discussions
- 154 Industry Talk
- 504 Announcements
- 5.4K Ideas & Feature Requests
- 85 Brandfolder
- 156 Just for fun
- 80 Community Job Board
- 512 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 308 Events
- 36 Webinars
- 7.3K Forum Archives
