sum if cell contains certain text

Question

{=SUM(--LEFT($A$692:$J$692&0,FIND(" ",$A$692:$J$692&" ")))}

I am using formula above to find the sum of the numbers in cells that also contain text (this formula returns 23 in the example below).  I would like to find the sum of all the cells that contain the same text (M in this example).  Find the sum only of the cells that contain “M”.  The result I would like in the example below is 8.

	<br>A	<br>B	<br>C	<br>D	<br>E	<br>F	<br>G	<br>H	<br>I	<br>J
<br>1	<br>4-Jul	<br>5-Jul	<br>6-Jul	<br>7-Jul	<br>8-Jul
<br>2	<br>7	<br>info	<br>10	<br>info	<br>16	<br>info	<br>22	<br>info	<br>28	<br>info
<br>3	<br>6	<br>info	<br>12	<br>info	<br>18	<br>info	<br>24	<br>info	<br>30	<br>info
<br>4	<br>3	<br>info	<br>14	<br>info	<br>20	<br>info	<br>26	<br>info	<br>32	<br>info
<br>5		<br>7 S		<br>5 T		<br>4 M		<br>3 S		<br>4 M

Answer 1

Hi,

Try this

=SUMPRODUCT((MID(A$692:$J$692,SEARCH(" ",A$692:$J$692,1)+1,1)="M")*(1*(LEFT(A$692:$J$692,SEARCH(" ",A$692:$J$692,1)))))

Answer 2

I can't get this to work, the result I am getting is #VALUE!