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

Speigel

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.

Paul Newcome

Try this...

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

Paul Newcome

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

Paul Newcome

Try this...

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

Speigel

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.

Paul Newcome

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

Speigel

@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.

Paul Newcome

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))

Speigel

@Paul Newcome

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.

Paul Newcome

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

Speigel

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

Paul Newcome

Happy to help. 👍️

Speigel

@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.

Speigel

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.

Paul Newcome

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

Speigel

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.