Home |
Search |
Today's Posts |
#1
|
|||
|
|||
COUNTIF - help please
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF - help please
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... |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF - help please
"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? |
#4
|
|||
|
|||
Quote:
|
#5
|
|||
|
|||
Quote:
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF - help please
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF - help please
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... |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF - help please
"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. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF - help please
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) |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF - help please
"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. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF - help please
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) |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF - help please
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... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
3d countif | Excel Worksheet Functions | |||
How do I use a countif function according to two other countif fu. | Excel Worksheet Functions | |||
edit this =COUNTIF(A1:F16,"*1-2*")+COUNTIF(A1:F16,"*2-1*") | Excel Discussion (Misc queries) | |||
COUNTIF or not to COUNTIF on a range in another sheet | Excel Worksheet Functions | |||
COUNTIF in one colum then COUNTIF in another...??? | Excel Worksheet Functions |