SUMIF

[Guest]

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

 

[N Harkawat]

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

 

[Guest]

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

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 ...

 

[Harlan Grove]

N Harkawat wrote...

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

....

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