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

Dennis M Wierzbicki · 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.

Paul Newcome · 05/19/22

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

Paul Newcome · 05/18/22

You actually want a SUMIFS.

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

Dennis M Wierzbicki · 05/18/22

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?

Dennis M Wierzbicki · 05/18/22

https://community.smartsheet.com/discussion/comment/330043#Comment_330043

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.

Paul Newcome · 05/18/22

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

Dennis M Wierzbicki · 05/18/22

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.

Paul Newcome · 05/18/22

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

Dennis M Wierzbicki · 05/18/22

https://community.smartsheet.com/discussion/comment/330046#Comment_330046

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?

Dennis M Wierzbicki · 05/18/22

https://community.smartsheet.com/discussion/comment/330049#Comment_330049

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.

Dennis M Wierzbicki · 05/18/22

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

Anybody with an alternate way to skin this cat?

Paul Newcome · 05/18/22

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.

Dennis M Wierzbicki · 05/18/22

https://community.smartsheet.com/discussion/comment/330068#Comment_330068

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

Paul Newcome · 05/19/22

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.

Dennis M Wierzbicki · 05/19/22

https://community.smartsheet.com/discussion/comment/330241#Comment_330241

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.

Paul Newcome · 05/19/22

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

Dennis M Wierzbicki · 05/19/22

https://community.smartsheet.com/discussion/comment/330254#Comment_330254

Hmm, let me think about how that might work. Thanks.

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

Best Answer

Answers

Categories