Posts tagged count

Count Text in a Range

Apr24
2009 Leave a Comment Written by admin

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 to count wildcard characters in a range, then you can write something like this:
=SUM(IF(NOT(ISERR(FIND("*",data))),1,0))

This formula should be entered as an array formula with Ctrl + Shift + Enter. Usually, wildcard can be “escaped” using a tilde (~), but the FIND function doesn’t understand wildcard characters like the SEARCH function does.

Posted in String Operations - Tagged , ,

Text Search Count

Jun29
2007 Leave a Comment Written by admin

If 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:

Posted in String Operations - Tagged ,

Count cells without formulae

Aug07
2006 Leave a Comment Written by admin

If you want to count cells in a given range without formulae use this function

Function CountNoFormula(rng As Range) As Long
Dim c As Range
   On Error GoTo CountNoFormula_Error
    For Each c In rng
        If Not c.HasFormula Then
            CountNoFormula = CountNoFormula + 1
        End If
    Next c
CountNoFormula_CleanUpExit:
   Exit Function
CountNoFormula_Error:
    CountNoFormula = CVErr(Err.Number)
    Resume CountNoFormula_CleanUpExit
End Function

If you want to count cells with formule then just remove then “Not” in front of c.HasFormula

Posted in Uncategorized - Tagged

Count number of asterisk (*)

Feb10
2005 Leave a Comment Written by admin

To count number of asterisk (*) in a column use this formula:
=COUNTIF(A1:A3, "=~*")

Posted in String Operations - Tagged ,