# Examine range, find specific value in a row, then return the value two columns over?

Options
✭✭✭✭✭
edited 05/18/22

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.

• ✭✭✭✭✭✭
Options

You should only have 5 columns based on my suggestion. Activity, Activity Subcategory, Resource, Resource Hours, Resource Cost.

• ✭✭✭✭✭✭
Options

You actually want a SUMIFS.

=SUMIFS({Column You Want To Sum}, {Column Housing Criteria}, @cell = [Picklist Column Name]@row)

• ✭✭✭✭✭
Options

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?

• ✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

You would need to set up multiple SUMIFS (one for each grouping of columns) and then add them together.

• ✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

I did look at your example above. You would set up a SUMIFS for each of the groupings and then add them together.

• ✭✭✭✭✭
edited 05/18/22
Options

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?

• ✭✭✭✭✭
edited 05/18/22
Options

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.

• ✭✭✭✭✭
edited 05/18/22
Options

@Paul Newcome , that worked a treat, but the formula required is pretty cumbersome. Thanks!

Anybody with an alternate way to skin this cat?

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭
edited 05/19/22
Options

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.

• ✭✭✭✭✭✭