Using Filter to Exclude Data from Calculations

**alexfwalker** · 03-25-2024, 10:08 AM

I have a complex table which is, to some extent, beyond my control as it's data pasted from another application. In an ideal world, I'd like to be able to pivot this data, but the structure of the data doesn't really allow for that.

So, my workaround is to use the formula below;

=SUMIFS(Data!O:O,Data!$BO:$BO,1)

This works perfectly well. However, I'd like to be able to use the drop down filters on the original data set and have my inclusions/exclusions reflected in this formula. I noted an article which referred to the 'LET' and 'visible' functions used together, but I'm afraid that I hit the wall of talent on trying to decipher it.

I would be grateful for any suggestions!

**AliGW** · 03-25-2024, 10:09 AM

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. The sample layout accurately matches that of your real data. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

**alexfwalker** · 03-25-2024, 10:28 AM

Demo workbook attached.

If I filter on Column AL in the Data tab (or any column on the Data tab, really), I'd like the values in Table B2:O12 to reflect my inclusions/exclusions.

**rorya** · 03-25-2024, 10:51 AM

If you can add a column to the right of the source data table using a formula like:
=SUBTOTAL(3,A2)
copied down, you can then add this in as another criterion to your existing formulas, filtering for 1 (visible rows will return 1, empty ones 0, as long as there is always data in that column for each row).

**Fluff13** · 03-25-2024, 11:09 AM

Using a helper col with the formula Rorya posted (I put it in col EV) In B2 on the Table sheet dragged right you could use

Formula:

Please Login or Register  to view this content.

**rorya** · 03-25-2024, 11:23 AM

Or clear B2:O12 and then enter this in B2:

=LET(sumData,Data!O:AB,criteria,Data!$BO:$BY,IsVis,Data!$EV:$EV,sq,SEQUENCE(COLUMNS(criteria)),DROP(REDUCE(0,sq,LAMBDA(status,current,VSTACK(status,BYCOL(sumData,LAMBDA(c,SUM(SUMIFS(c,INDEX(criteria,0,current),{1,2},IsVis,1))))))),1))

**alexfwalker** · 03-25-2024, 12:00 PM

Superb, thank you! The first solution posted worked perfectly, thank you.

**Fluff13** · 03-25-2024, 12:15 PM

Glad to help & thanks for the feedback.

Using Filter to Exclude Data from Calculations

LinkBack

Thread Tools

Rate This Thread

Display

Using Filter to Exclude Data from Calculations

Re: Using Filter to Exclude Data from Calculations

Re: Using Filter to Exclude Data from Calculations

Re: Using Filter to Exclude Data from Calculations

Re: Using Filter to Exclude Data from Calculations

Re: Using Filter to Exclude Data from Calculations

Re: Using Filter to Exclude Data from Calculations

Re: Using Filter to Exclude Data from Calculations

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Exclude one Value from a Filter

Filter/sort data, which has a pivot table and other calculations (vlookups/sumifs/etc)

Sumifs exclude cells that are hidden with data filter

How to Filter Data Into A Second Worksheet to Perform Calculations On That Filtered Data

Checkbox to exclude numbers from calculations

[SOLVED] Worksheet Calculations Exclude Custom Formula

Bookmarks

Bookmarks

Posting Permissions