How to Count Cells with Question Marks (?)

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:

ContentStep 1Step 2Step 3
???ABCDEFG?1TRUE1
???ABCDEFG1TRUE1
ABCD??EFG???5TRUE1
ABC#VALUE!FALSE0
123#VALUE!FALSE0
A#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