SUMIF function

  • Thread starter Thread starter Kathryn
  • Start date Start date
K

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).
 
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.
 
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
'========
 
Or, you could just use the * wildcard.
=SUMIF(A17:W17,"*V*",A17:W17)
Click to expand...

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
 
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
 
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
 
Ah yes, I did indeed. I should know better, to read the post more carefully
if the anwser seems so simple.
Thanks
Elkar
 
Back
Top