A question mark is a punctuation mark that indicates an interrogative clause. In Excel, a question mark is a wildcard representing one character. You can use the COUNTIF or SUMPRODUCT functions to count the cells with the question marks.
Example: You are working on a worksheet with the content in column A.
Question: How many cells have only one question mark?
To count the number of cells with only one question mark, you can use the COUNTIF function together with a tilde (~).
=COUNTIF(A2:A12,"~?")
The result returns 2, so two cells contain only one question mark (?): A9 and A12.
If you do not include a tilde in the formula, the result will return the number of cells with one character.
Question: How many cells have question marks?
To count the number of cells with question marks, you can use the COUNTIF function together with a tilde (~) and the wildcard asterisks (*).
=COUNTIF(A2:A12,"*~?*")
The result returns 7, so seven cells contain question marks (?).
Alternatively, you can use the SUMPRODUCT function to find the cells with question marks (?).
=SUMPRODUCT(–(ISNUMBER(FIND("?", A2:A12))))
=SUMPRODUCT((ISNUMBER(FIND("?", A2:A12)))*1)
Explanation:
- Step 1: FIND("?", A2:A12): To find question marks in the cells. The result returns a number if there are question marks; otherwise, it returns an error;
- Step 2: (ISNUMBER(FIND("?", A2:A12))): The result returns TRUE when the cell contains the question marks; 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 |
| A | #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.