Sum values based on multiple criteria (comma delimited) in a single cell

Hello all...

I'm trying to find out if there is a function that will allow me to sum the values for a designated set of critieria that may have one or more values in a cell.

Attached is a spreadsheet that contains two worksheets, [data] and [target].

The [data] tab contains the data set to be used with two columns, (code) and (balance).

The [target] tab contains the results of my "query" with three columns, (code set), (month), and (value).

In the [target] tab, the (code set) column contains the criteria I want to "filter" the data set by. sometimes there will only be one criteria identified. But other times, there may be two or more criteria identified and separated by commas.

In the [target] tab, the (value) column and first two rows, I've used the SUMIF function where if the "one" identified criteria is found it will total the sum.

However, for the last row, how do I get the SUMIF function to recognize that there are multiple criteria values that need to be found then summed?

Any guidance would be greatly appreciated.

ML

In C2:

=SUMPRODUCT(--ISNUMBER(SEARCH(data!$A$2:$A$20,A2)),data!$B$2:$B$20)

copied down.

Okay, use:

=SUMPRODUCT(--ISNUMBER(SEARCH(data!$A$2:$A$20&" ",SUBSTITUTE(A2,","," ")&" ")),data!$B$2:$B$20)

It is possible, yes, but first why would there not be results for rows 2, 6, 7, 8?

Then:

Please Login or Register  to view this content.

or shorter, but requires confirmation with CTRL+SHIFT+ENTER not just ENTER...

Please Login or Register  to view this content.

Post a more realistic workbook sample....

Did you know you have a bunch of blank rows after 16410 and then you have more data... is that supposed to be there?

That is why the formula doesn't work... it sees the blanks as part of the count....

To avoid the blank in the REJ1 column, then try:

Please Login or Register  to view this content.

but I don't get 80271 in D2.. I get 105760 (there are other departments in the mix... your pivot table shows only 3 and sums only those 3)

I assume the original comma delimitations still apply.. that is why you are not using Pivot Table to do this all?

If you now that column B won't contain blanks, then you can remove this part:

--(data!$B$2:$B$25000<>"") in both occurances within the formula... 1 less thing to check and therefore that much more efficient and quicker...

See next post. Sorry, I don't see a delete post....

This user defined function I wrote will handle this issue:

Please Login or Register  to view this content.

Then put this in the cell you want to see the total: '= AddCommaString (a1)' .

This example will add up cell a1.

Welcome to the forum,

The thread is a bit old now.... but that's okay if you are offering a solution...

although, you need to wrap all code in code tags per forum rules.