sumif containing certain letters

[ml1990ml]

hi, is there a way and how to find certain text in A1:C4 and then sum numbers next to it (result in column F)? For example like this:

ths-60	dc-60	dc-60		ths	120 ?
dc-60	tmppa-60	tmppa-120		dc	180 ?
tmppa-30		ths-60		igr	240 ?
igr-240				tmppa	210 ?

<tbody>
</tbody>

ps: I use excel 2010

 

[FormR]

Here is one way based on your sample data:

Excel Workbook
	A	B	C	D	E	F
1	ths-60	dc-60	dc-60		ths	120
2	dc-60	tmppa-60	tmppa-120		dc	180
3	tmppa-30		ths-60		igr	240
4	igr-240				tmppa	210
Sheet1

 

[barry houdini]

I used this array formula in F1 copied down

=SUM(IF(LEFT(A$1:C$4,LEN(E1)+1)=E1&"-",SUBSTITUTE(A$1:C$4,E1&"-","")+0))

confirmed with CTRL+SHIFT+ENTER

 

[ml1990ml]

thank u both, works briliant :D

 

[Rick Rothstein]

If you are using XL2007 or above, then here is another array-entered** formula you can consider...

=SUM(IFERROR(1*SUBSTITUTE(A$1:C$4,E1&"-",0),))

**Confirm using CTRL+SHIFT+ENTER and not just Enter by itself

 

[ml1990ml]

thank u. now i dont know which one to use, they all work great cheers