Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
=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 |
#3
|
|||
|
|||
"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 ... |
#4
|
|||
|
|||
N Harkawat wrote...
=SUMPRODUCT(--(MID(A1:A6,3,1)="X"),(B1:B6)) .... =SUMIF(A1:A6,"??X*",B1:B6) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to use SUMIF to return sums between two values located in cells | Excel Worksheet Functions | |||
How do I use the sumif if I have multiple criteria (i.e. greater . | Excel Worksheet Functions | |||
Dynamic sumif function | Excel Worksheet Functions | |||
SUMIF Not | Excel Discussion (Misc queries) | |||
SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions |