SUMIF
 

Hi,

I want to put a sumif formula, but only recognising one character in a
string of text.

For example:

£
CLX75 - 10
CLX69 - 20
CLS65 - 5
CLT12 - 65
CLX59 - 151
CLT60 - 15

I want to return the total via a sumif on those that contained the letter X
(3 rd character of the text). So the answer would 181.

Can you please help.

Thanks

--
hm

=SUMPRODUCT(--(MID(A1:A6,3,1)="X"),(B1:B6))


"Mehta Mehta" wrote in message
...
Hi,

I want to put a sumif formula, but only recognising one character in a
string of text.

For example:

£
CLX75 - 10
CLX69 - 20
CLS65 - 5
CLT12 - 65
CLX59 - 151
CLT60 - 15

I want to return the total via a sumif on those that contained the letter
X
(3 rd character of the text). So the answer would 181.

Can you please help.

Thanks

--
hm

"Mehta Mehta" wrote:

Hi,

I want to put a sumif formula, but only recognising one character in a
string of text.

For example:

£
CLX75 - 10
CLX69 - 20
CLS65 - 5
CLT12 - 65
CLX59 - 151
CLT60 - 15

I want to return the total via a sumif on those that contained the letter X
(3 rd character of the text). So the answer would 181.

Can you please help.

Thanks

--
hm

Using your example data in cell A1 with this macro the result is 181:-

Sub cond_sum()
total = 0
For a = 1 To 6
If Mid(Cells(a, 1), 3, 1) = "X" Then
total = total + Cells(a, 2)
End If
Next a
Cells(8, 2) = total
End Sub

If it helps ...

N Harkawat wrote...
=SUMPRODUCT(--(MID(A1:A6,3,1)="X"),(B1:B6))
....

=SUMIF(A1:A6,"??X*",B1:B6)