formula needed to extract only those values not hidden by filter

[tbablue]

Hi forum,

I have a table with table slicers.

Is there any way (by use of formula) to extract only those values in a prescribed field that are visible after slicers are employed? I want to capture these in a different sheet.

I use this formula to sum the associated values from Planning[Wk#1]- but I want a detailing of the actual items from Planning[Demand] also.

=SUMPRODUCT((Planning[[Demand]:[Demand]]=1)*(SUBTOTAL(9,OFFSET(AE32,ROW(Planning[Wk'#1])-MIN(ROW(Planning[Wk'#1])),0))))

Any assistance most gratefully rec'd.

Thx in advance

 

[johnny51981]

Change your SUBTOTAL(9 to SUBTOTAL(109

Also change your MIN to a SUBTOTAL function as well, using function 105.

109 is still a SUM, but this is the function number used to only calculate visible rows.
105 is the MIN equivalent as well

SUBTOTAL function - Microsoft Support

How to use the SUBTOTAL function in Excel to return a subtotal in a list or database.

support.microsoft.com

 

[Fluff]

What exactly do you mean by this

but I want a detailing of the actual items from Planning[Demand]

As they will all be 1

 

[tbablue]

Change your SUBTOTAL(9 to SUBTOTAL(109

Also change your MIN to a SUBTOTAL function as well, using function 105.

109 is still a SUM, but this is the function number used to only calculate visible rows.
105 is the MIN equivalent as well

SUBTOTAL function - Microsoft Support

How to use the SUBTOTAL function in Excel to return a subtotal in a list or database.

support.microsoft.com

Thanks for your attention - this only gives me a number - I need to see a list of the actual items in a field that remain unhidden.

For example, in the same table, I have a field called Planning[Cost Centre Name] - I need a way to extract a list of those items.

Sorry if my initial post wasn't clear.

 

[tbablue]

What exactly do you mean by this

As they will all be 1

Apols - you are quite correct.

Let me change the field.

In the same table, I have a field called Planning[Cost Centre Name]. I need a formula that will extract a list of items from that field (for example) when the table is filtered - all items in Planning[Demand] are either 1 or 0 so I can use those but I can't figure out how to implement something like INDEX and SUBTOTAL(9,OFFSET(AE32,ROW(Planning[Wk'#1])-MIN(ROW(Planning[Wk'#1])),0))) in partnership.

 

[Fluff]

Are you happy using a helper column?

 

[tbablue]

Are you happy using a helper column?

Absolutely - anything to get me over this hurdle.

 

[Fluff]

In that case you can use

=SUBTOTAL(3,[@Demand])

Change the column to any column that will always have a value & then you can use (I've called the helper column "IsVisible")

=FILTER(Planning[Cost Centre Name],(Planning[IsVisible]=1)*(Planning[Demand]=1))

You can also change your formula to

=SUMIFS(Planning[Wk'#1],Planning[Demand],1,Planning[IsVisible],1)

 

[tbablue]

In that case you can use

=SUBTOTAL(3,[@Demand])

Change the column to any column that will always have a value & then you can use (I've called the helper column "IsVisible")

=FILTER(Planning[Cost Centre Name],(Planning[IsVisible]=1)*(Planning[Demand]=1))

You can also change your formula to

=SUMIFS(Planning[Wk'#1],Planning[Demand],1,Planning[IsVisible],1)

So, I've introduced a field into my table Planning[IsVisible] that is entirely populated with 1.

How do I implement SUBTOTAL(3,Planning[@Demand])?

 

[Fluff]

Did you put the subtotal formula into the IsVisible column?