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

Options
✭✭
edited 08/19/21

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.

• ✭✭✭✭✭✭
Options

Try this...

=SUMIFS(SumRange:SumRange, Criteria:Criteria, CONTAINS([Criteria Range]@row, @cell))

• ✭✭✭✭✭✭
Options

Try changing the Criteria column over to a mutli-select as well.

• ✭✭✭✭✭✭
Options

Try this...

=SUMIFS(SumRange:SumRange, Criteria:Criteria, CONTAINS([Criteria Range]@row, @cell))

• ✭✭
Options

Yes, that worked @Paul Newcome . I see your help frequently on here and I knew I'd find you eventually. :)

Let me add one level of complexity and see what your thoughts are...

Within the cell which is our Criteria Range @row ... What if that cell also had multiple results? Is there a way to have it look at the values between each "," or will it only see that as a full text string?

See attached and how rows 2 and 3 are the same independent values but together as a string only one is matching.

• ✭✭✭✭✭✭
Options

My suggestion at that point would be to change the fields to multi-select dropdowns and then replace the CONTAINS function with HAS.

• ✭✭
Options

@Paul Newcome Yeah I had that thought also... but the multiple codes is also a join() formula that collects results from another area to create the string. This wouldn't separate into separate multi select options. I'm probably at a loss with this part. But you answered my first part perfectly. Thanks.

• ✭✭✭✭✭✭
Options

If you replace the ", " delimiter with

CHAR(10)

You will have the strings delimited via a line break which is what the multi-selects use which will in turn generate the separate multi-select options.

=JOIN({Range}, CHAR(10))

• ✭✭
Options

A) That's awesome... I had no idea.

B) Got it setup... not bringing the results. See image... what am I doing wrong? Second image shows the result of that formula at 0.

• ✭✭✭✭✭✭
Options

Try changing the Criteria column over to a mutli-select as well.

• ✭✭
Options

@Paul Newcome Perfect... that fixed it! Awesome! Thank you so much!

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

• ✭✭
edited 10/25/21
Options

@Paul Newcome This item is closed but I've ran across a new problem with it that I'm hoping you can help with.

So with the above solution (that worked) I ended up using the Criteria Range as YearDay values and in my lookup I'm searching for ... contains...

So the problem is let's say I have a value of \$100 on year day 360... in my daily spend plan I get \$100.00 on year day 3, 6, 36, 60 and 360. It's not searching for exactly the 360, but any finding of those characters.

How can I only search for the exact number 360 and not the individual characters that make up 360?

UPDATE: I added Char(34) before and after my result so I think it's working now. If you have other input... I appreciate it.

• ✭✭
edited 10/25/21
Options

So update: I tried to add quotes to the criteria range so the cell would read "360" rather than 360. Interestingly enough if I type "360" into the cell it works. If I have that as a result of a formula (the same "360" ) it doesn't work.

• ✭✭✭✭✭✭
Options

@Speigel If you convert the yearday column to a multi-select dropdown, you could incorporate the HAS function.

• ✭✭
Options

Thanks for the help Paul. Not sure if you saw my comment above about Char(34) that fixed it prior to your last comment or I could have gone down that path too. Usually more than one way to skin a cat as they say. Thanks again.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!