Check if the column contains a value in Excel

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

The following example contains a list of 10 numbers.

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

=COUNTIF(A2:A11,">0")

It counts cells with 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 have to 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 last value inside the data set.

=COUNTIF(A2:A1048576,">0")

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 the future versions of Excel, you have to change the formula again.

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

=COUNTIF(A:A,">0")

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:

=COUNTIFS(A:A,">0", A:A,"<7")

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