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.