Sum, collect or contains in a range with text against a criteria field of combined text.

Speigel ✭✭
edited 08/19/21 in Formulas and Functions

Typically you use an SumIf to search a set of cells (range) and it's contents for a single criteria code. Example looking for the number 1 within cells containing 1, 2 and 3... with a result or sum of 1. Or if you have multiple contents in those cells you can use contains, or has or collect to gather the results to sum. I'm doing the reverse.

I have a column where each cell is a single code. Example: Cell 1 is "A1", Cell 1 is "B2"... etc.

The criteria for each rows formula is a combined text string of things like "A1, B2, C3".

I need to gather the values of all rows that have any of the combined codes and sum that.

Maybe the image will help explain.

Best Answers


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!