COUNTIF function
Learn how to count non-specific characters with the COUNTIF function.
The format of the COUNTIF function is
=COUNTIF(範囲,検索条件)
It comes to. Here, try specifying “other than ○○” in [Search condition].
Before that, as a preparation, let’s create a COUNTIF function below cell E2 that counts how many cells each color has in column B of the table below.
=COUNTIF($B$2:$B$11,D2)
Note that even if a blank cell (D5) is adopted in [Search Condition], it is not recognized.
If you want to recognize it, enter “‘” in cell D5.
“Except for” basic form
Next, in cell F2, specify how many cells are “non-green” in B2:B10.
In this case, the search condition is
"<>緑"
is specified. 「<>” (the opposite of “=”). Enclose the two in ““.
=COUNTIF(B2:B11,"<>緑")
Adopt cells for “other than” designation
Next, modify the calculation formula created in cell F2 so that it can be copied to F5.
Change the Search Condition from Non-Green to Except Cell D2. Here
"<>" & D2
and so on. When specifying a cell, do not enclose it in “”. Write on the outside.
Also”<>” and D2 are not considered as one condition as they are, so they must be combined with a half-width “&”.
Therefore, change it as follows: Make [Range] an absolute reference considering copying it below.
=COUNTIF($B$2:$B$11,"<>"&D2)
Copy the calculation formula created in F2 to F5 and check the result.
When specifying “other than”, the blank field in cell D5 is correctly recognized.
If you are interested, enter a half-width “‘” in cell D5.
