Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count - but exclude counting cells with a formula
What formula do I need to count a range where the cells contain a formula.
The cell range formula returns a text value. example: A1:A10 contain a formula that will return a text result A11 contains a formula to return the number of cells that have a result in in A1:A10 Any formulas I have tried count the formulas also. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count - but exclude counting cells with a formula
Assume that you are having value in A Column Like this..
A Column ABC DEF 5646 ZYX 65466 Try this formula in B1 cell for finding the Text Values from A1:A5. =COUNTA(A1:A5)-COUNT(A1:A5) =COUNTA(A1:A5) will count the Total number cells which is having any value from A1:A5 =COUNT(A1:A5) will count the Total number of cells which is having the Numeric Values from A1:A5 But the Counta function will count the cells which is having only the space also. If this post helps, Click Yes! -------------------- (Ms-Exl-Learner) -------------------- "Gotroots" wrote: What formula do I need to count a range where the cells contain a formula. The cell range formula returns a text value. example: A1:A10 contain a formula that will return a text result A11 contains a formula to return the number of cells that have a result in in A1:A10 Any formulas I have tried count the formulas also. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count - but exclude counting cells with a formula
Thank you for the help.
this the test formula I tried: =COUNTA(A1:A11)-COUNT(A1:A10) A3 is the only cell that contains a text value the formula returned 11 when it should have only returned 1 the formula seems to count all the cells containing a formula and adds any that has a value returned by them this the formula "Ms-Exl-Learner" wrote: Assume that you are having value in A Column Like this.. A Column ABC DEF 5646 ZYX 65466 Try this formula in B1 cell for finding the Text Values from A1:A5. =COUNTA(A1:A5)-COUNT(A1:A5) =COUNTA(A1:A5) will count the Total number cells which is having any value from A1:A5 =COUNT(A1:A5) will count the Total number of cells which is having the Numeric Values from A1:A5 But the Counta function will count the cells which is having only the space also. If this post helps, Click Yes! -------------------- (Ms-Exl-Learner) -------------------- "Gotroots" wrote: What formula do I need to count a range where the cells contain a formula. The cell range formula returns a text value. example: A1:A10 contain a formula that will return a text result A11 contains a formula to return the number of cells that have a result in in A1:A10 Any formulas I have tried count the formulas also. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count - but exclude counting cells with a formula
Hi Gotroots,
What do you get in the cell when there is no text result? If you get a blank try: =COUNTA(A1:A10)-COUNTIF(A1:A10,"") This counts all entries then subtracts the blanks. The entry "" at the end can be changed to whatever you get when there is no text. Squeaky "Gotroots" wrote: What formula do I need to count a range where the cells contain a formula. The cell range formula returns a text value. example: A1:A10 contain a formula that will return a text result A11 contains a formula to return the number of cells that have a result in in A1:A10 Any formulas I have tried count the formulas also. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count - but exclude counting cells with a formula
That sorted that one!
Thanks Squeaky "Squeaky" wrote: Hi Gotroots, What do you get in the cell when there is no text result? If you get a blank try: =COUNTA(A1:A10)-COUNTIF(A1:A10,"") This counts all entries then subtracts the blanks. The entry "" at the end can be changed to whatever you get when there is no text. Squeaky "Gotroots" wrote: What formula do I need to count a range where the cells contain a formula. The cell range formula returns a text value. example: A1:A10 contain a formula that will return a text result A11 contains a formula to return the number of cells that have a result in in A1:A10 Any formulas I have tried count the formulas also. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
exclude formulas when counting cells | Excel Worksheet Functions | |||
need this formula to exclude cell value 'x' and 'xx' when counting | Excel Discussion (Misc queries) | |||
How do I exclude cells from a formula? | Excel Discussion (Misc queries) | |||
Pivot table, how do you exclude counting cells with formulas as a | Excel Worksheet Functions |