If you’ve ever used functions like Vlookup or Sumif in Google Sheets with criteria in multiple rows, you may have encountered the problem of extra blank cells in ArrayFormula output. These blank cells can cause issues, especially when you enter values in them, resulting in an #REF! error. In this article, we’ll explore why this happens and provide solutions to remove these extra blank cells.
The Reason for Extra Blank Cells in ArrayFormula Output at the Bottom
Let’s start by understanding why these extra blank cells appear. We’ll take the example of a SUMIF formula in cell G2. In this formula, the criteria range is F2:F3, but we have mistakenly included the entire column F in the formula. This leads to extra blank cells in the output when values are entered.
As you can see, even though there are no criteria in cells F4:F, the SUMIF treats them as blank criteria and includes them in the calculation.
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 the IF+LEN combination or the IF+ISBLANK combination. These combinations help restrict the visual output of the formula to cells containing the criteria while removing the blank cells below the output.
Here’s an example using the IF+ISBLANK combination:
=ArrayFormula(if(isblank(F2:F),"",(sumif(B2:B,F2:F,C2:C))))
The root cause of this issue lies in the IF+LEN or IF+ISBLANK combo. While they visually restrict the output, they also generate blank cells below the output.
How to Remove Blank Cells in ArrayFormula Output
Removing extra blank cells in ArrayFormula output is formula-specific. Let’s go through a few examples to illustrate how to remove them for different functions.
Remove Extra Blank Cells in SUMIF in Google Sheets
For a SUMIF formula, you can use the FILTER function to remove the blank cells in the criteria range. Make sure there are no blank cells within the selected criteria range.
=ArrayFormula(sumif(B2:B,filter(F2:F,F2:F<>" "),C2:C))
Remove Extra Blank Cells in Vlookup in Google Sheets
In the case of Vlookup, the formula may return #N/A instead of blank cells below the output. To convert these #N/A errors to blank values, you can use the IFERROR function.
=iferror(ArrayFormula(vlookup(E2:E9,A2:C9,2,FALSE)))
Alternatively, you can use the FILTER function to remove the blank cells below the formula output.
=ArrayFormula(vlookup(FILTER(E2:E,E2:E<>" "),A1:C,2,FALSE))
Remove Extra Blank Cells in Row ArrayFormula Output at the Bottom
When using the ROW function to generate sequential numbers, the formula may return extra blank cells below the output. In this case, you can use the QUERY or SORTN function to remove these extra blank cells.
=Query(ArrayFormula(if(len(B2:B),row(B1:B),)),"Select * where Col1 is not null",0)
=sortn(if(len(B2:B),row(B1:B),),max(if(len(B2:B),row(B1:B),),0))
The SORTN function is useful if you are not concerned about sorting the output.
Tips to Eliminate Extra Rows in the Data Range Itself
Functions like SORT and UNIQUE don’t take criteria. To eliminate blank cells or rows below such formula outputs, you can follow these steps:
- Filter the source data to eliminate the blank cells/rows at the bottom.
=filter(A1:A,len(A1:A))
- Use the filtered data as the source in the UNIQUE formula.
=unique(filter(A1:A,len(A1:A)))
In conclusion, there is no one-size-fits-all formula to remove extra blank cells in ArrayFormula output in Google Sheets. Depending on the formula used, you can employ functions like FILTER, QUERY, or SORTN to achieve the desired result. Just remember, the function LEN plays a vital role in these combinations.
For more tips and tricks on mastering Google Sheets, visit Crawlan.com.
Happy spreadsheet-ing!
