SUMIF, how can I sum values based only on certain

Question

In my range A1:B25 I have categories (A1:A25) and numbers (B1:B25). The cells in column A contains numbers seperated by comma "1,2,3,11,1056,392" etc.

This formula:

=SUMPRODUCT(ISNUMBER(FIND("," & 1 & ",";"," & SUBSTITUTE(A1:A25;" ";"") & ","))*1)

is counting all the cells which contains the number "1" and excludes "11", "101" etc.

Perfect!

But I'm looking for a formula that gives me the cells containing "1" in column A and then sums all the corresponding cells in column B.

Like this:

=SUMIF("," & A1:A25 & ",";"," & C1 & ",";B1:B25)

C1 is my check-up-value ("1")

How can I make this work?

Answer 1

Assuming all your numbers in column A are separated by only commas, This formula should do the trick:

=SUMPRODUCT(--(ISNUMBER(FIND(",1,",","&A1:A25&","))),B1:B25)

Hope this helps!