Forum Discussion
Solved
Filter result to skip selected columns
Hi all Question - when outputting a filter, can selected columns be skipped in the output? Please see my example spreadsheet. I basically want to skip column H and have the total populate in ...
Try below:
- Select the Range: Highlight the range of cells that you want to include in your filter, excluding the column you want to skip.
- Apply Filter: Click on the "Data" tab, and then select "Filter." This will apply a filter to your selected range.
- Custom Formula for Total:
- Click on the cell where you want the total to appear.
- Enter a custom formula to add up the values in the columns, while skipping column x.
=SUM(A2:G10)+SUM(I2:I10) - This formula adds up all the values in columns A to G and I, skipping column H.
- Adjust Range if Necessary: If your data range changes, adjust the range in the formula accordingly.
- Apply and Sort Filters: You can now apply filters and sort your data as needed, while keeping the total formula intact.
This takes elements from your formula, but breaks the calculation down into steps using LET rather than nesting functions. Each LET variable serves much the same purpose as a helper range in legacy Excel but without introducing clutter.
=LET(
selectedRecords, FILTER(Table1, Table1[INCLUDE?] = option),
sortedRecords, SORT(selectedRecords, 3, -1),
requiredFields, CHOOSECOLS(EXPAND(sortedRecords, , 6, ""), {1, 6, 4}),
requiredFields
)