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

Content | Step 1 | Step 2 | Step 3 |
---|---|---|---|

***ABCDEFG*** | 1 | TRUE | 1 |

***ABCDEFG | 1 | TRUE | 1 |

ABCD**EFG*** | 5 | TRUE | 1 |

ABC | #VALUE! | FALSE | 0 |

123 | #VALUE! | FALSE | 0 |

FALSE | #VALUE! | FALSE | 0 |

TRUE | #VALUE! | FALSE | 0 |

* | 1 | TRUE | 1 |

** | 1 | TRUE | 1 |

*** | 1 | TRUE | 1 |

* | 1 | TRUE | 1 |

**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.