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:

ContentStep 1Step 2Step 3
***ABCDEFG***1TRUE1
***ABCDEFG1TRUE1
ABCD**EFG***5TRUE1
ABC#VALUE!FALSE0
123#VALUE!FALSE0
FALSE#VALUE!FALSE0
TRUE#VALUE!FALSE0
*1TRUE1
**1TRUE1
***1TRUE1
*1TRUE1

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.

Leave a Reply