In a previous post I explored how to ignore blank cells when using the `QUERY` function in Google Sheets, which had its own distinct way of removing blank cells from the data capture.

But how do you ignore blank cells in your spreadsheet when you’re not in a `QUERY` function? What if you’re in one of the many different `IF` functions such as `IF`, `IFS`, `SUMIF`, `SUMIFS`, `COUNTIF`, `COUNTIFS`, `AVERAGEIF`, `AVERAGEIFS`, `MAXIFS`, `MINIFS` (etc). How do you write a condition that checks if a cell is blank in those functions?

## Ignore Blank Cells With Ranges

**To ignore blank cells with functions operating on a criteria_range and a corresponding criterion is to write the condition "<>" in the criterion.**

For example, in the `AVERAGEIF` function, which uses a **criteria_range** as its first parameter and a corresponding **criterion** as its second parameter to exclude calculating the average of a range if a criteria range cell is blank, then use the condition `"<>"` in the criterion as follows:

A | B | C | |
---|---|---|---|

1 | 6 | Confirmed | |

2 | 9 | ||

3 | 10 | Pending | |

4 | 5 | Confirmed | =AVERAGEIF(B1:B4,"<>",A1:A4) |

`A1:A4`if range

`B1:B4`does

**not have blank value**. The formula above will ignore using

`A2`in its calculations and return

`7`.

If we wanted to reverse the condition, such that cells which **do** include blank cells are used in our calculations, then we would remove the `<>` characters in our criterion parameter.

Using the same formula above, if we wanted the average of values where the criterion is based on blank cells, then we would write:

A | B | C | |
---|---|---|---|

1 | 0 | =IF(A1<>"", 1, 0) | 1 |

2 | FALSE | =IF(A2<>"", 1, 0) | 1 |

3 | 0 | =IF(LEN(A3), 1, 0) | 1 |

4 | FALSE | =IF(LEN(A4), 1, 0) | 1 |

5 | 0 | =IF(ISBLANK(A5), 1, 0) | 0 |

6 | FALSE | =IF(ISBLANK(A6), 1, 0) | 0 |

7 | 0 | =IF(NOT(ISBLANK(A7)), 1, 0) | 1 |

8 | FALSE | =IF(NOT(ISBLANK(A8)), 1, 0) | 1 |

9 | 0 | =IF(A9, TRUE, FALSE) | FALSE |

10 | FALSE | =IF(A10, TRUE, FALSE) | FALSE |

11 | =IF(A11, TRUE, FALSE) | FALSE | |

12 | <space> | =IF(A12, TRUE, FALSE) | #VALUE! |

`IF(cell)`check against 0, FALSE, and empty cell (which is the correct result) and a space.

Using the value `"<>"` to ignore blank cells on a range is handy, but what if we wanted to use the same formula over an array of cells where we are checking the condition on **one cell** instead of a range of cells.

## Ignoring Blank Cells – `IF` Formula

There are several ways to ignore blank cells when using the `IF` formula. Here is a short list:

IF FunctionFirst Parameter | Example Usage |
---|---|

A1<>"" | =IF(A1<>"", TRUE, FALSE) |

LEN(A1) | =IF(LEN(A1), TRUE, FALSE) |

ISBLANK(A1) | =IF(ISBLANK(A1), TRUE, FALSE) |

NOT(ISBLANK(A1)) | =IF(NOT(ISBLANK(A1)), TRUE, FALSE) |

`IF`function

## Warning! Don’t Do This…

Do not use the condition `=IF(A1, TRUE, FALSE)` to test if a cell **is blank**, as there are false-like values that could be contained in the cell being referenced which would return `FALSE`.

In the table below we test how each formula goes with checking if the cell is **not blank**. Column A contains the false-like values, Column B contains the formula and Column C the result of the formula in Column B:

A | B | C | |
---|---|---|---|

1 | 0 | =IF(A1<>"", 1, 0) | 1 |

2 | FALSE | =IF(A2<>"", 1, 0) | 1 |

3 | 0 | =IF(LEN(A3), 1, 0) | 1 |

4 | FALSE | =IF(LEN(A4), 1, 0) | 1 |

5 | 0 | =IF(ISBLANK(A5), 1, 0) | 0 |

6 | FALSE | =IF(ISBLANK(A6), 1, 0) | 0 |

7 | 0 | =IF(NOT(ISBLANK(A7)), 1, 0) | 1 |

8 | FALSE | =IF(NOT(ISBLANK(A8)), 1, 0) | 1 |

9 | 0 | =IF(A9, TRUE, FALSE) | FALSE |

10 | FALSE | =IF(A10, TRUE, FALSE) | FALSE |

11 | =IF(A11, TRUE, FALSE) | FALSE | |

12 | <space> | =IF(A12, TRUE, FALSE) | #VALUE! |

`IF(cell)`check against 0, FALSE, and empty cell (which is the correct result) and a space.

As you can see from the above snapshot from the spreadsheet table, using the simple `IF(cell, TRUE, FALSE)` formula does provide some incorrect and problematic results. Therefore, because of this don’t use the simple formula when checking if a cell has blank values.

## Summary

When checking for blank cells in your `IF` formulas use any of the following conditions in your criterion parameter:

`cell<>""``LEN(cell)``ISBLANK(cell)`

If you’re criterion is based on a range and you are using one of the multiple formulas containing `IF`, such as `SUMIF`, `SUMIFS`, `AVERAGEIF`, `AVERAGEIFS`, `COUNTIF`, `COUNTIFS`, `MAXIFS`, `MINIFS` (etc) then to ignore cells that are blank use the criterion `"<>"`.

If you’re using the QUERY function in Google Sheets we have a similar article on how to ignore blank cells too.