Check if a Column Contains a Value in Excel

With the COUNTIF function, you can count the number of cells that meet a single condition.

Example

The following example contains a list of 10 numbers.

If you want to look for values larger than 0, you can write this formula:

It counts cells that are 1 or higher. There are 6 such numbers.

Check if the column contains a value

There is the following problem with this approach: it counts numbers from A2:A11. If you want to add additional numbers, for example, in cell A12, you must change the formula.

You can keep changing it to higher and higher numbers, or add the number of the last row in a column, which is 1048576.

It’s hard to memorize, so you can easily access it by pressing Ctrl + Down Arrow. This shortcut moves the cursor to the last row inside a sheet. It only works for columns that have no value between the cursor and the last row, otherwise, the cursor will move to the last value inside the data set.

If you make a mistake and type a number that is too high, Excel will return the #NAME? error.

There is an additional problem. If the number of rows increases in future versions of Excel, you have to change the formula again.

The best way to check if the value is in the column is to use column reference, A:A.

This formula returns the same result and is safer and more readable than that of the previous examples.

Check if the column contains multiple values

The COUNTIF function checks a single condition. If you want to use multiple criteria, you can modify the formula:

This function uses multiple criteria to determine whether a column contains a specific value.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.