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.