Say that you have a formula that is returning values from a range of data. When someone uses your worksheet and then applies a filter to hide certain rows, they might expect that your formula would only return the values that are still visible after applying the filter. Most Excel functions will continue to include the hidden rows in the calculation.
In Figure 1, the formula in E2 is using TEXTJOIN to return a list of products from column B with each product separated by a comma.
In Figure 2, someone has filtered the data to show only the rows where column C contains the word, “Yes.” You will see that the result in E2 continues to show “Cherry” from B5 even though this value is no longer visible.
One limited approach would be to repeat the filter logic inside the formula. If you are sure that the data will always be filtered to show where the Keep column is “Yes,” then you could use =TEXTJOIN(",",TRUE,IF(C3:C28="Yes",B3:B28,"")). In this formula, the TRUE in the second argument says to ignore empty values. When the third argument of the IF function returns "", Excel treats that as an empty value and the value is skipped as shown in Figure 3.
Most of Excel’s calculation functions do not differentiate between hidden and visible rows. The only two functions that can ignore hidden rows are SUBTOTAL and FILTER.
Say that the data could possibly be filtered by many possible criteria and you want a flexible formula that will return all of the visible cells in column B.
Before applying a filter, add a helper column to your data. As shown in E3 of Figure 4, the formula =SUBTOTAL(3,B3) will count how many cells in B3 are visible and contain a number or text.
That is a funny sentence and perhaps you should read it again. The formula in E3 is counting how many cells in B3 are visible and contain a number or text. Of course, the answer here is going to be 1 because B3 contains text and is visible. When you copy this formula down to all rows, all of the visible rows will contain a 1 in column E.
At this point, you can apply any filter to any or all columns. As a row gets hidden by the filter, the one in column E will change to a zero. It is somewhat difficult to show you the zero in the hidden rows (because the act of unhiding the row would change the zero back to a one.) However, you can use the SUM function as shown in E30 of Figure 5. This formula is adding all of the visible and hidden values from E3 to E28. The seven visible rows are showing ones and total to 7. That means that the 19 hidden rows must have changed to a zero.
Now that you have the helper column in E showing one for only the visible rows, you can use a variety of formulas to return the list of visible products.
=TEXTJOIN(",",TRUE,IF(E3:E28,B3:B28,"")) will return all of the values from E where the corresponding value in E is not zero or False.
You could replace the IF with a FILTER function:
=TEXTJOIN(",",,FILTER(B3:B28,E3:E28)) will return all of the values from B where the corresponding value in E is not zero or False.
Note that both SUBTOTAL and AGGREGATE are testing for hidden rows. There is no function in Excel that can test for hidden columns.
