# How to Count Cells with Asterisks (*)

An asterisk in Excel is a wildcard representing characters. You can use the COUNTIF or SUMPRODUCT functions to count cells with the asterisks.

Example: You are working with a worksheet with the content in column A.

Question: How many cells have only one asterisk?

To count the number of cells with only one asterisk, you can use the COUNTIF function together with a tilde (~).

=COUNTIF(A2:A12,"~*")

The result returns 2, so two cells contain only one asterisk (*): A9 and A12.

If you use the asterisk without the tilde, the result returns the number of cells with text.

Question: How many cells have asterisks?

To count the number of cells with asterisks, you can use the COUNTIF function together with a tilde (~) and the wildcard asterisk (*).

=COUNTIF(A2:A12,"*~**")

The result returns 7, so seven cells contain asterisks (*).

Alternatively, you can use the SUMPRODUCT, ISNUMBER, and FIND functions to find the cells with asterisks (*).

=SUMPRODUCT(--(ISNUMBER(FIND("*", A2:A12))))

=SUMPRODUCT((ISNUMBER(FIND("*", A2:A12)))*1)

Explanation:

• Step 1: FIND("*", A2:A12): To find asterisks in the cells. The result returns a number if there are asterisks; otherwise, it returns an error;
• Step 2: (ISNUMBER(FIND("*", A2:A12))): The result returns TRUE when the cell contains the asterisks; otherwise, it returns FALSE;
• Step 3: The double hyphen (or times one) converts TRUE into one and FALSE into zero;
• Step 4: The SUMPRODUCT function returns the sum of the products of the new array.

The following are the details from step 1 to step 3:

Notes: The SUMPRODUCT Function

The SUMPRODUCT function adds all the multiplication results for all arrays.

Formula:

=SUMPRODUCT(array1, [array2], …)

Explanations:

– Array1 is required; the first array is to multiply and add.
– Array2 is optional; the second array is to multiply and add.