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

On Mon, 23 Jan 2012 19:59:28 +0000, Amada wrote:

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

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com
Shikata ga nai...

"Amada" wrote:
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?

Sorry - but it doesn't work (Excel 2007)

Sorry but it has Error in Value

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
--
Amada

On Tue, 24 Jan 2012 15:18:39 +0000, Amada wrote:

Stan Brown;1214641 Wrote:
On Mon, 23 Jan 2012 19:59:28 +0000, Amada wrote:-

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") )

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.

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com
Shikata ga nai...

"Stan Brown" wrote:
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.

No matter. This approach is unnecessarily complicated.

Amanda, see the equivalent COUNTIF solution that I provided.

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)

"Rick Rothstein" wrote:
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'"

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.

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)

On Tue, 24 Jan 2012 23:17:31 -0800, joeu2004 wrote:

"Stan Brown" wrote:
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.

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com
Shikata ga nai...