Counting instances of text in a table column with filters applied.

[vanwooten]

The attached picture shows column W with W7:W7 containing a list and color formatting. The table column below (Trades[Trade Category]) uses the list above for a drop down and I have Conditional formatting to match the colors. The percentages to the left are meant to be the percent of each category being displayed. I have the formula blow which works so long as no filter is applied to the table. The formulas dont give an error but they give unpredictable results when the table is filtered. I need to tweak the formula so that it works when the table is filtered.

=SUMPRODUCT((Trades[Trade Category]=W2)*(SUBTOTAL(103,OFFSET(W2,ROW(Trades[Trade Category])-MIN(ROW(Trades[Trade Category])),0))))/SUBTOTAL(103,Trades[Trade Date])

Trades[TradeDate] is present in every record.

 

[vanwooten]

I have seen a solution that looks close but I dont understand it. I suspected that I might need a helper column.

Subtotal function with Text Criterias

I am trying to do a subtotal of the number with multiple criteria. E.g. with the sample data below, there will be filters applied to the table, say date filters. I am looking for the Subtotal of Color = "Red", Fruit = "Apple", where I should get the answer 18. Any idea how I can achieve...

techcommunity.microsoft.com