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.

--
Biff
Microsoft Excel MVP


"Kathryn" wrote in message
...
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).

Or, you could just use the * wildcard.

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

HTH
Elkar


"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"Kathryn" wrote in message
...
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).

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
'========
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Kathryn" wrote in message
...
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).

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

--
Biff
Microsoft Excel MVP


"Elkar" wrote in message
...
Or, you could just use the * wildcard.

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

HTH
Elkar


"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"Kathryn" wrote in message
...
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).

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
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
'========
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Kathryn" wrote in message
...
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).

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

"Kathryn" wrote in message
...
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).

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

"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"Elkar" wrote in message
...
Or, you could just use the * wildcard.

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

HTH
Elkar


"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"Kathryn" wrote in message
...
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).