If you choose criteria in multiple rows in functions like Vlookup, Sumif, etc. then you must want to know how to remove extra blank cells in ArrayFormula output. As the use of criteria in infinite range may return extra blank cells at the bottom of your formula output.

This cause one major issue. Even if the cells seem blank below the output, not below entire row, when you enter any value in any of those cells, the formula would return an #REF! error.

You May Like: Different Error Types in Google Sheets and How to Correct It.

In the below SUMIF formula in G2, I have the criteria in the range F2:F3. But in the SUMIF I have chosen F2:F. See the above-said issue surfacing when I enter a value below.

Before addressing the issue of removing extra blank rows in ArrayFormula output, we must know how this happens.

The Reason for Extra Blank Cells in ArrayFormula Output at the Bottom

With the help of SUMIF, I am going to explain this. This will be applicable to other formulas too. See the SUMIF formula that is in use in cell G2.

Must Check: Google Sheets Functions Guide.

=ArrayFormula(if(len(F2:F),(sumif(B2:B,F2:F,C2:C)),))

Why I have used the below formula in SUMIF?

if(len(F2:F)

See what happens when I am not using the above in SUMIF.

Actually, there is no criteria in F4:F. But the SUMIF treats this range contains criteria which are blank and sums the column C accordingly.

How to Remove Blank Cells in Criteria Range

To remove the blank cells in the criteria range (not in the formula output), you can use either IF+LEN combination or IF+ISBLANK combination.

You have already seen the IF+LEN combination in SUMIF. Below is the equivalent IF+ISBLANK combination.

=ArrayFormula(if(isblank(F2:F),"",(sumif(B2:B,F2:F,C2:C))))

The root cause of the issue that I have mentioned in the title is associated with this IF+LEN or IF+ISBLANK combo. This combo helps us to restrict the formula output visually in cells that contain the criteria. But unfortunately returns blank cells below the output.

If you ask me how to remove extra blank cells in ArrayFormula output, my answer is it’s formula specific.

Here are few formula examples starting with the above SUMIF.

How to Remove Blank Cells in ArrayFormula Output

I am including a few formula examples that cover some of the popular functions.

Remove Extra Blank Cells in SUMIF in Google Sheet

I am using the function Filter to filter out blank cells in the criteria that cause the extra zeroes and ultimately the blank cells.

=ArrayFormula(sumif(B2:B,filter(F2:F,F2:F<>""),C2:C))

In this make sure that you are not leaving any blank cells in between the selected criteria range.

Remove Extra Blank Cells in Vlookup in Google Sheets

The Vlookup returns #N/A instead of blank cells below the formula output. Here normally we use IFERROR to convert those #N/A errors to blank values.

=iferror(ArrayFormula(vlookup(E2:E9,A2:C9,2,FALSE)))

Here again, we can use the Filter to remove the blank cells below the formula output.

=ArrayFormula(vlookup(FILTER(E2:E,E2:E<>""),A1:C,2,FALSE))

The Filter function won’t work in all scenarios. Here are such few examples and solutions.

Remove Extra Blank Cells in Row ArrayFormula Output at the Bottom

As you may know, the ROW function can fill a column with sequential numbers.

As an example;

=ArrayFormula(row(A1:A10))

This formula would return the sequential number from 1 to 10 in a column.

You can incorporate the IF+LEN combination with this formula too.

This Row formula would also return extra blank cells from cell A8 onwards. Here we can use Query or Sortn to remove the extra blank cells below the output.

=Query(ArrayFormula(if(len(B2:B),row(B1:B),)),"Select * where Col1 is not null",0)

The use of SORTN is tricky to remove the extra blank cells. It’s easy to use but it sorts the formula output. So if you are not concerned about sorting, use the SORTN to remove the extra blank cells below the output.

Here is the SORTN based formula.

=sortn(if(len(B2:B),row(B1:B),),max(if(len(B2:B),row(B1:B),),0))

This Sortn will limit the number of rows to the max output of Row function which is 6. That means there would be values in 6 cells in the row function output and that serve the purpose.

Tips to Eliminate Extra Rows in the Data Range Itself

Functions like SORT, UNIQUE don’t take criteria. To eliminate blank cells or rows below such formula outputs we can follow the below tip.

Here I am considering the UNIQUE function for example.

Steps:

Filter the source data to eliminate the blank cells/rows at the bottom.

=filter(A1:A,len(A1:A))

Then use this as the source data in Unique.

=unique(filter(A1:A,len(A1:A)))

Conclusion:

In concise, there is no single formula that we can depend to remove extra blank cells in ArrayFormula output in Google Sheets.

In some cases, we can use Filter, and in some other cases, we can use Query or SORTN. What important is, in all combinations, the function LEN plays a vital role!