Counting text in a range is easy. For example, if you would like count how many times “text” is in a specified range (named data), you can write something like this: =COUNTIF(data,”*text*”) This formula will give you the count of all the cells where Excel found “text” at any place. But let’s say you want […]
Continue readingIf you want to do a text search count in a range, you can use the following formulas:Where D3 is the search text and the range A2:A16 houses the data. An array formula using SUM and SEARCH {=SUM(IF(ISERROR(SEARCH(D3,A2:A16)),0,1))} Another using COUNTIF: =COUNTIF(A2:A16,”*”&D3&”*”) This picture might make it clear:
Continue readingIf you want to count cells in a given range without formulae use this function Function CountNoFormula(rng As Range) As LongDim c As Range On Error GoTo CountNoFormula_Error For Each c In rng If Not c.HasFormula Then CountNoFormula = CountNoFormula + 1 End If Next cCountNoFormula_CleanUpExit: Exit FunctionCountNoFormula_Error: CountNoFormula = CVErr(Err.Number) Resume CountNoFormula_CleanUpExitEnd Function If you want to count cells with formule […]
Continue readingTo count number of asterisk (*) in a column use this formula: =COUNTIF(A1:A3, “=~*”)
Continue reading