Summing text, numbers in same cell

[rrowe]

Can someone help me understand why the Total column does no total correcly? It should sum up to 11 instead of 10. The formula for it is shown below. The formula for the Paired column works ok. The numbers and text may reside in the same cell, and I need to sum them. Thanks!

Paired Total
1p 1p 2p p p p p 7 10


=SUMPRODUCT(--ISNUMBER(FIND("p",B3:H3,1)))+SUMPRODUCT(--ISNUMBER(FIND("pp",B3:H3,1)))+SUMPRODUCT(--ISNUMBER(FIND("ppp",B3:H3,1)))+SUMPRODUCT(--ISNUMBER(FIND("1p",B3:H3,1)))+SUMPRODUCT(--ISNUMBER(FIND("2p",B3:H3,1)))

 

[Charlie45]

Try breaking down you formula.

=SUMPRODUCT(--ISNUMBER(FIND("p",B3:J3,1))) finds 7 p

=SUMPRODUCT(--ISNUMBER(FIND("pp",B3:J3,1))) finds 0 pp

=SUMPRODUCT(--ISNUMBER(FIND("ppp",B3:J3,1))) finds 0 ppp

=SUMPRODUCT(--ISNUMBER(FIND("1p",B3:J3,1))) finds 2 1p

=SUMPRODUCT(--ISNUMBER(FIND("2p",B3:J3,1))) finds 1 2p

7+2+1 = 10

 

[barry houdini]

What are the possible values in B3:H3 and what does each "score"? If there's a limited number of possibilities you might be better off with a COUNTIF based formula, e.g.

=SUM(COUNTIF(B3:H3,{"p","pp","ppp","1p","2p"})*{1,2,3,2,3})

 

[rrowe]

Thanks Charlie, but I need to add the value of the 2p, which would be a value of 3.
2+p=3 which is the value of the cell. I know that makes it more complicated. Thanks for the assistance.

 

[rrowe]

Barry, you da man! That works great.

Thanks Barry and Charlie45 for the support.