Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
counting "="
Is there a way to make something like =countif($a$1:$a$100,"=") work as
intended? The function is returning zero when there are clearly cells with the text "=" in it. If i were to use something like $a:$a as the range, it will just count all the blank cells in column A. Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
counting "="
Try =COUNTIF(A:A,"*=*")
best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "rockhammer" wrote in message ... Is there a way to make something like =countif($a$1:$a$100,"=") work as intended? The function is returning zero when there are clearly cells with the text "=" in it. If i were to use something like $a:$a as the range, it will just count all the blank cells in column A. Thanks. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
counting "="
I think it's because the = sign is not text but part of the formula.
Although I also can't get it to work when I make it text. However, this code does work: Dim Counter, c Counter = 0 For Each c In Selection If c.Formula Like "*=*" Then Counter = Counter + 1 End If Next c MsgBox (Counter) So VBA can handle it at least. My code assumes you select your range and want output to a message box, but you could change it to be a pre-determined range and put the result in a cell. I don't understand why the Excel function doesn't work on an = sign as a string, but I am not surprised it doesn't work when its part of a formula because then it's not part of the value of the cell. And I know that Excel functions don't seem to have a contains or like feature, which VBA does have. Hope this helps. Keith "rockhammer" wrote: Is there a way to make something like =countif($a$1:$a$100,"=") work as intended? The function is returning zero when there are clearly cells with the text "=" in it. If i were to use something like $a:$a as the range, it will just count all the blank cells in column A. Thanks. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
counting "="
It just occured to me that you may want to distinguish between cells with =
in the formula and cells with = in the value. If you only want cells with = in the value you could change the code in my previous post from c.Formula Like "*=*" to c.Value Like "*=*" Keith "rockhammer" wrote: Is there a way to make something like =countif($a$1:$a$100,"=") work as intended? The function is returning zero when there are clearly cells with the text "=" in it. If i were to use something like $a:$a as the range, it will just count all the blank cells in column A. Thanks. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
counting "="
Thanks, Keith & Bernard.
*=* is perfect since I'd like to avoid macros and I've verified that it works. "Bernard Liengme" wrote: Try =COUNTIF(A:A,"*=*") best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "rockhammer" wrote in message ... Is there a way to make something like =countif($a$1:$a$100,"=") work as intended? The function is returning zero when there are clearly cells with the text "=" in it. If i were to use something like $a:$a as the range, it will just count all the blank cells in column A. Thanks. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting the occurrence of "Y" & "N" in Excel 2007 | Excel Worksheet Functions | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel |