COUNTIF - help please

[Amada]

In on cell I have following formula =COUNTIF($N8:$EP8, "V")
It counts cells which contain "V"
I have to modify this formula - and need your help.
In my cells ($N8:$EP8) I can have for example 1V or 2V or 6V
How to count "Vs" , for example, one cell "2V" other cell "5V"
resoult = 7
It should counts only numbers with suffix "V"
THanks in Advanc

 

[Stan Brown]

In on cell I have following formula =COUNTIF($N8:$EP8, "V")
It counts cells which contain "V"
I have to modify this formula - and need your help.
In my cells ($N8:$EP8) I can have for example 1V or 2V or 6V
How to count "Vs" , for example, one cell "2V" other cell "5V" -
resoult = 7
It should counts only numbers with suffix "V"
THanks in Advance

=sumproduct( 0+right($N8:$EP8,1)="V") )

The apparently redundant "0+" forces Excel to treat the logical
expression as a numeric one: TRUE becomes 1 and FALSE becomes 0.

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[joeu2004]

In on cell I have following formula =COUNTIF($N8:$EP8, "V") [....]
It should counts only numbers with suffix "V"

Ostensibly:

=COUNTIF($N8:$EP8, "*V")

Caveat: That counts anything ending in "V" (as well as "V" along), not just
__numbers__ followed by "V". Okay?

 

[Amada]

=sumproduct( 0+right($N8:$EP8,1)="V") )

The apparently redundant "0+" forces Excel to treat the logical
expression as a numeric one: TRUE becomes 1 and FALSE becomes 0.

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Sorry but it has Error in Valu

 

[Rick Rothstein]

Give this formula a try...

=SUMPRODUCT((RIGHT($N8:$EP8)="V")*(CODE(RIGHT($N8:$EP8,2))>47)*(CODE(RIGHT($N8:$EP8,2))<57))

Rick Rothstein (MVP - Excel)


"Amada" wrote in message In on cell I have following formula =COUNTIF($N8:$EP8, "V")
It counts cells which contain "V"
I have to modify this formula - and need your help.
In my cells ($N8:$EP8) I can have for example 1V or 2V or 6V
How to count "Vs" , for example, one cell "2V" other cell "5V" -
resoult = 7
It should counts only numbers with suffix "V"
THanks in Advance

 

[Stan Brown]

Sorry but it has Error in Value

I don't know what you mean by "error in value", but I can assure you
that I tried it myself before posting and it was just fine. Try
copy-pasting instead of retyping.

Please remember to trim quotes when posting a follow-up.

 

[joeu2004]

[....]
I don't know what you mean by "error in value" [....]
,
Try copy-pasting instead of retyping.

Perhaps you should try following your own advice. It might surprise you.

No matter. This approach is unnecessarily complicated.

Amanda, see the equivalent COUNTIF solution that I provided.

 

[Rick Rothstein]

Amanda, see the equivalent COUNTIF solution that I provided.

I am not sure that is what she wants given that she included this sentence
in her original posting...

"It should counts only numbers with suffix 'V'"

On the other hand, the formula I posted does what this sentences seems to be
asking for.

Rick Rothstein (MVP - Excel)

 

[joeu2004]

I am not sure that is what she wants given that she included
this sentence in her original posting...
"It should counts only numbers with suffix 'V'"

I raised that very point in my initial response to Amanda. It is also
unclear that Amanda intended to exclude cells with non-numbers followed by
"V", or in fact all of her cells are of the form "number" or "numberV", and
she wants to exclude those that are not "numberV".

Anyway, those are questions for Amanda to answer. If she wants to exclude
non-numbers followed by "V", you have provided an adequate formula for that
purpose.

In the quoted response to Stan above, I did not say my COUNTIF formula met
Amanda's needs.

I said it was an "equivalent" solution. Equivalent to what, you might ask
thoughtfully? I think that is clear in the original context: equivalent to
Stan's SUMPRODUCT alternative, which does not distinguish what precedes "V"
at the end, if anything.

 

[Rick Rothstein]

In the quoted response to Stan above, I did not say my COUNTIF

formula met Amanda's needs.

I said it was an "equivalent" solution. Equivalent to what, you
might ask thoughtfully? I think that is clear in the original
context: equivalent to Stan's SUMPRODUCT alternative,
which does not distinguish what precedes "V" at the end,
if anything.

Sorry, my fault... I misunderstood what you were referring to when you said
"equivalent" and, with that misunderstanding in mind, I didn't want Amanda
to draw a wrong conclusion about what your COUNTIF formula did.

Rick Rothstein (MVP - Excel)

 

[Stan Brown]

Stan Brown;1214641 Wrote:
=sumproduct( 0+right($N8:$EP8,1)="V") )

[....]
I don't know what you mean by "error in value" [....]
,
Try copy-pasting instead of retyping.

Perhaps you should try following your own advice. It might surprise you.

Thanks. Amanda, I'm sorry; I'm not sure where the needed left
parenthesis disappeared to, because I did indeed copy/paste.
However, as Joe says:

No matter. This approach is unnecessarily complicated.

Amanda, see the equivalent COUNTIF solution that I provided.

Yes, his approach is simpler and therefore, I think, better.