Microsoft Excel Functions & Formulas

Use the SUM, MID, and COLUMN functions to

count specific characters in a range

In this example, we want to count specific characters that appear in

a range. Use the MID function to extract each character from the

cells, then define the range to be searched using the COLUMN

function. The SUM function counts the result. Combine all these

functions into one array formula.

4

To count certain characters in a range:

1. In cells A2:A11 list IP addresses.

2. Insert in any cells one or more characters like x or xxx.

3. Select cell D2 and type the following array formula:

=SUM((MID(A1:A11,COLUMN(1:1),3)="xxx")*1).

4. Press <Ctrl+Shift+Enter>.

5. Select cell D3 and ...