Need to compare data from string of text in 1 cell to a column of data in another

Question

I am trying to do an index match function in which I concatenated two text into a string and compared it to the same text in another table. Once comparing the two tables, I am able to pull a number result. However, I can't seem to figure a way to process a long string. In the score on column C it should add up all the values that are associated with the date and test.

EX:

Answer 1

In your posted sample data, you have discrepancies:

• Your example data shows the concatenated values in cell B2 to be "test1" "test 2" and "test 3"
• However, in column G, it will only find a match for "test1" because there are no spaces for "test2" and "test3"

Assuming such discrepancies don't exist in your actual data, and that there would be exact matches because the concatenation is the result of a formula and not done by hand, you should be able to use this formula in cell C2 and copy down to get the sums you're looking for. Adjust the ranges to suit your actual data:

=SUMPRODUCT(--(COUNTIF(B2,"*"&$G$2:$G$100&"*")>0),--($F$2:$F$100=A2),$H$2:$H$100)