98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsForum home » Delegate support and help forum » Microsoft Excel Training and help » advanced excel class - Excel Maths
advanced excel class - Excel Maths
Resolved · Low Priority · Version Standard
Karen has attended:
Access Introduction course
Excel VBA Intro Intermediate course
Excel Maths
How do you write an equation to count a cell if it has a text content
RE: Excel Maths
Hi Karen
You need to use an array function as you are dealing with an array of cells.
Type the following into the cell where you want the value to go BUT do not press enter
=SUM(IF(ISTEXT(RANGE),1,0))
where range is the range of cells you want to test.
Then hold down the shift and the control key and then press enter. (This is the standard technique for entering an array function).
Your function will appear in the function bar in curley brackets
{=SUM(IF(ISTEXT(RANGE),1,0))} and should return the answer you are looking for.
The IF(ISTEXT(RANGE),1,0)) bit just checks each cell and returns 1 if the cell contains text. and the Sum bit just adds it all up
One small point, if you have a cell that has been explicitly formatted as text, then it will be counted as text even if you then type a number in. To fix this just explicitly reformat it as a number
Hope this helps
Training information:
See also:
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Reconciling a list to correspond with anotherMay have a list that needs to make but on another sheet one list seems to be out, eg. product list one sheet contains all product and inventory data, while the other contains pricing data. Both need to match with all products but there is more products in one list than the other. To find the disparaging product compare data in the two columns that need to match Make sure that order the same way. |