Currently trying to create a SUMIF formula that recognises the partial text in a range and returns the sum of the number reflecting it.

enter image description here


Research & Results

I have looked into SUMIF for partial text: =SUMIF(B2:B3,"*"&B5&"*",C2:C3) which returns 0.

I have also looked into using LEFT(C2:C3,1) : =SUMIF(B2:B3,"*"&B5&"*",LEFT(C2:C3))

However returns N/A.


Any advice on this would be appreciated

Sample Script

1 Answer 1


Try this formula:

=index(transpose(query({split(B2,"|");split(C2:C3,"|")},"select sum(Col1),sum(Col2)")))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.