Are you familiar with the SUMIF formula in Google Sheets? It’s a powerful tool for conditional sum calculations. But do you know how to use the SUMIF formula while excluding hidden rows? In this article, we’ll explore a handy technique to achieve this without relying on a helper column.
The Challenge of Dealing with Hidden Rows
While Google Sheets provides the SUBTOTAL function to handle hidden rows, it doesn’t allow for adding conditions or criteria. This is where we need to combine SUMIF and SUBTOTAL to exclude hidden rows effectively.
Using a Helper Column
Traditionally, users employ a combination of SUMPRODUCT, SUBTOTAL, ROW, and OFFSET in Excel to achieve this task. However, this method doesn’t work as expected in Google Sheets. To learn more about it, check out our post on the Subtotal Function With Conditions in Excel and Google Sheets. There, we also cover the use of Countif, Averageif, Minifs, Maxifs, and Sumif in visible rows without a helper column in Excel.
The Solution: SUMIF and SUBTOTAL Combination
The SUMIF function is designed to sum a range based on a condition. However, it adds values from hidden rows if the condition matches, which is not what we want. Unfortunately, neither Google Sheets nor Excel have a built-in function to deal with hidden rows based on conditions. Enter the SUMIF and SUBTOTAL combination.
Using a Helper Column
One approach is to create a helper column for the calculation. Let’s walk through the steps to apply conditional sum while excluding hidden rows in Google Sheets.
- In your Google Sheet, create a helper column (let’s say column A) next to your data.
- In cell A2, enter the following formula:
=SUBTOTAL(109, E2) - Copy the formula from cell A2 and paste it down to A7 to apply it to the entire range.
The key here is the SUBTOTAL function. It pulls all the values from column E into column A. When you hide any rows, the corresponding SUBTOTAL formula will return zero instead of the actual value. With this feature, we can omit hidden row values from our total sum.
To calculate the sum of column E based on the condition in column B and exclude hidden rows, use the following formula:
=SUMIFS(E2:E7, A2:A7, ">0", B2:B7, "Coverall")This formula will return the sum of the cell range E2:E7 if the values in column B equal “Coverall” and the corresponding values in column A are greater than zero. By hiding any row that contains “Coverall,” you’ll witness the magic of excluding hidden rows from the total.
Without Using a Helper Column
If you prefer not to use a helper column, there’s another method to achieve the same result. In this non-helper column approach, we’ll create a virtual helper column using curly brackets and the SUBTOTAL function.
- Delete the helper column (column A) created earlier.
- Adjust the criteria column (Item) to column A and the value column (Amount) to column D.
- Use the following formula to exclude hidden rows without a helper column:
=SUMIFS(D2:D7, {SUBTOTAL(109, D2); SUBTOTAL(109, D3); SUBTOTAL(109, D4); SUBTOTAL(109, D5); SUBTOTAL(109, D6); SUBTOTAL(109, D7)}, ">0", A2:A7, "Coverall")This formula may appear complicated, but in reality, it’s straightforward. The formula within the curly brackets acts as a virtual helper column.
To create this virtual helper column:
- In any blank cell, enter the following formula:
=JOIN(";", E2:E7) - Copy the output of the formula and paste it as a value in the same cell.
- Enclose the pasted text in curly braces to transform it into an array. It should look like this:
{subtotal(109, D2); subtotal(109, D3); subtotal(109, D4); subtotal(109, D5); subtotal(109, D6); subtotal(109, D7)}
Now you can delete the helper column (column E). This method saves time compared to the previous one and is suitable for datasets with fewer than 15 rows.
A Dynamic Approach Using BYROW
The non-dynamic approach we discussed above has two limitations: it takes time to create the virtual helper column, and it’s only practical for smaller datasets. However, we can overcome these limitations by using the BYROW function, a LAMBDA helper function in Google Sheets.
Replace the virtual helper column formula with the following BYROW formula in the SUMIFS function:
=SUMIFS(D2:D7, BYROW(D2:D7, LAMBDA(helper, SUBTOTAL(109, helper))), ">0", A2:A7, "Coverall")This dynamic approach is my recommended formula for excluding hidden rows in Google Sheets using SUMIF.
Now you have two methods at your disposal to conditionally sum values while excluding hidden rows in Google Sheets. Choose the approach that suits your needs and make your data analysis more efficient!
For more Google Sheets tips and tricks, visit Crawlan.com.
Original article by Crawlan
