SUMIF function

[Kathryn]

I want to use sumif on a row cells containing something like 2V, 4V, 1V, 3S,
8S, etc. I want to add the cells that have V in them.
I have tried using =SUMIF(A17:W17,"V",A17:W17) but it doesn't seem to work.
I'm wondering if the sum range is bothered by the letters in the cells (V or
S).

 

[T. Valko]

Try this array formula** :

=SUM(IF(RIGHT(A17:W17)="V",--SUBSTITUTE(A17:W17,"V","")))

Assumes that every cell that contains a V also contains a number.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[Elkar]

Or, you could just use the * wildcard.

=SUMIF(A17:W17,"*V*",A17:W17)

HTH
Elkar

 

[Don Guillett]

This will sum any cell in the range that has a "v" anywhere, before or after
the number. For v at the end, try
if right(c,1)="v" then

'=========
Option Compare Text
Sub sumnumbersintext()
ms = 0
For Each c In Range("a1:b21")
If InStr(c, "v") Then
ms = ms + Replace(c, "v", "")
End If
Next
MsgBox ms
End Sub
'========

 

[T. Valko]

Or, you could just use the * wildcard.

=SUMIF(A17:W17,"*V*",A17:W17)

I think you misundstood what they want.

A17 = 2V
B17 = 4V
C17 = 1V
D17 = 3S
E17 = 8S

=SUMIF(A17:W17,"*V*",A17:W17) returns 0

Array entered:

=SUM(IF(RIGHT(A17:W17)="V",--SUBSTITUTE(A17:W17,"V","")))

Returns 6

 

[Don Guillett]

Or a function entered =snt(a1:b17,"v")

Function snt(rng, ltr)
snt = 0
For Each c In rng
If InStr(c, ltr) Then
snt = snt + Replace(c, ltr, "")
End If
Next
End Function

 

[Ashish Mathur]

Hi,

You can try this array formula (Ctrl+Shift+Enter)

=SUM(IF((RIGHT(A17:W17)="V"),--LEFT(A17:W17))). The answer will be 7

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Elkar]

Ah yes, I did indeed. I should know better, to read the post more carefully
if the anwser seems so simple.
Thanks
Elkar