• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer

Script Everything

  • Spreadsheets
  • Python
  • About
You are here: Home / Apps / Ignore Blank Cells In IF Functions – Google Sheets

Ignore Blank Cells In IF Functions – Google Sheets

April 7, 2021 by Ryan Sheehy

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 7
=AVERAGEIF(B1:B4,"<>",A1:A4)
Get the average of values in range 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 that 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 6 Confirmed
2 9
3 10 Pending
4 5 Confirmed 9
=AVERAGEIF(B1:B4,"",A1:A4)
Get the average of values in range A1:A4 if range B1:B4 DOES have blank values .

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 check if a cell is blank. Here is a shortlist of operators and functions that can assist:

IF Function
First 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)
How to check if a cell is or is not blank when using the standard IF function

The first and last examples in the table above do the same thing: they check if A1 is NOT blank/empty .

The second example checks if the cell contains a length. If there is a length, then it is assumed there is something within the cell, however, you might want to read this post as some characters do not have a length !

The third example is a simple function that determines if the cell is blank.

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 show the formula and the result:

A B
1 FALSE
=IF(A1, TRUE, FALSE)
2 FALSE FALSE
=IF(A2, TRUE, FALSE)
3 0 FALSE
=IF(A3, TRUE, FALSE)
Just checking if a cell contains something might not work as expected, both rows 2 & 3 contain something, but these values are false-y.

As you can see from the above spreadsheet, using the simple formula =IF(cell, TRUE, FALSE) does provide some incorrect and problematic results if the assumption behind the formula is to just check if something exists in the cell .

Therefore, because this formula can be misleading don’t use it when checking if there is a value in the cell. Use the alternative formulas such as LEN(cell) or ISBLANK(cell) .

A B
1 TRUE
=IF(ISBLANK(A1), TRUE, FALSE)
2 FALSE TRUE
=IF(LEN(A2), TRUE, FALSE)
3 0 TRUE
=IF(LEN(A3), TRUE, FALSE)
Better to use formulas that better describe what you’re doing when checking for blank cells.

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 your 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 .

Filed Under: Apps

Primary Sidebar

Hello! My name is Ryan Sheehy the author of ScriptEverything.com

This website acts as a second brain of sorts for all the hacks and explorations I find when trying to solve problems at work.

About Me

Footer

Spreadsheets

I have been using spreadsheets since as early as 1996 and I continue to use this great productivity tool on a daily basis.

Check out some of my most recent discoveries about spreadsheets.

Python Code

I have been using Python since the late 1990’s due to the limitations of Excel’s VBA. I enjoy being able to wrangle and fetch data externally using Python.

Discover more of what I’ve found with Python.

Apps

Sometimes I play, hack and tinker with other applications to scratch an itch.

To find out the latest hack check out my blog feed.

Copyright © 2023 ScriptEverything.com

  • Contact
  • Privacy Policy