Help with Formulas for a Filtered Table

[Jar Head]

Hi All - This is my first post requesting help with a formula. I did some digging prior but didn't find a solution. Please open attached spreadsheet for request / problem.

Thank you,
JH

Request: I want Columns D and F to represent their respective totals whether the table is filtered or not. D3 should represent total of distinct Candidates (the total in D3 should adjust as the table is filtered).

F2,3,4 totals should represent the number of distinct Candidates (from Column B) with their respective color and adjust as the table if filtered E.g. "Suzy Woozy" is interviewing 4 distinct Candidates, 1 of which is Blue and 3 are Red. I want the Column F totals to show those values when the table if filtered for Suzy Woozy

[TMS]

For D3, change SUBTOTAL(3? ) to SUBTOTAL(103?)

For column F, you probably need to look at AGGREGATE instead of SUMPRODUCT.

[WideBoyDixon]

I'm on O365 but these should work:

Please Login or Register  to view this content.

Note that the formula in D3 is an array formula entered with Ctrl+Shift+Enter.

WBD

[Jar Head]

Thank you all for jumping in so quickly to help. WBD - I plugged your formulas in and they are 100% bomber! Thanks a ton!

[Jar Head]

I spoke too soon - there is still one unresolved issue with the filtered table totals. Notice that when you filter by Interviewer (Column A), the totals in Column F don't match the distinct values. E.g. in the image below, we should have a total of 3 Red distinct Candidates not 5. Column F is counting instances of a value (color) instead of counting distinct "Candidates" and their corresponding color totals.
Any ideas on how to solve?

Here is the formula in F3 I'm using from WideBoyDixon: =SUMPRODUCT(SUBTOTAL(3,OFFSET(IntvwData[Color Code],ROW(IntvwData[Color Code])-ROW(IntvwData[#Headers])-1,,1))*(IntvwData[Color Code]=$E3))