Forum Discussion

matt0020190's avatar
matt0020190
Brass Contributor
Jan 19, 2025

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 column I instead.

 

Is this possible please?

 

Thanks

Formulas and Functions
  • Kidd_Ip's avatar
    Kidd_Ip
    Jan 19, 2025

    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.
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Jan 19, 2025

    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
)

     

  • matt0020190's avatar
    matt0020190
    Brass Contributor
    Jan 19, 2025

    Thanks for your suggestion and reply.

    Is it possible to amend my existing formula in the example spreadsheet opposed to taking the approach above using a separate formula?

  • Kidd_Ip's avatar
    Kidd_Ip
    MVP
    Jan 19, 2025

    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.

Resources