Forum Discussion
Solved
Using Filters to Auto-Update Complex Formulas
Hi,
This is my first post here. Hoping someone here may be able to assist me.
I have the below spreadsheet. There is currently 500 rows of data. At the Top is a Summary Grid with formulas summarizing the data below (Rating, Employee Level, etc. ) I'm also showing an example of one of the formulas in Cell F3. The other cells contain similar formulas.
Ask: How do I get the Summary Grid to only display the data based on the rows visible, which is determined by Column T filter selections. For example, if the user selects "Level 4 Manager 2" and this manager has 10 employees, how do I get the Summary Grid to display just the data based on those 10 employees (versus the total 500).
I've seen videos/sites that use the Subtotal or Aggregate formula, but it appears those can only be applied to specific functions, not a more complex formula.
Is this possible?
It is
=COUNTIFS( A$16:A$4202,"*8*", Table[IsFiltered], 1 )or
=COUNTIFS( A$16:A$4202,"*8*", Z$16:Z$4202, 1 )
6 Replies
If you add helper column IsFiltered to the table, e.g. =AGGREGATE(3,5,[@ID]), you may modify your formulae as
=COUNTIFS( <current part>, Table[IsFiltered], 1 ) / COUNTIFS( <another part>, Table[IsFiltered], 1)
or like, not sure about divider.
Abstract field ID shall have no blanks. If you have nos uch column, you could add another helper column with, for example, =ROW() in each cell.
Above is for structured table, similar is for ranges.
Hi Sergei, thank you for responding to my inquiry. Per your suggestion I created a titled "IsFiltered" in Column Z. I attempted to modify the numerator portion of your suggestion, but the "Table[IsFiltered]" is being highlighted by Excel as not working.
=COUNTIFS(COUNTIF(A$16:A$4202,"*8*"), Table[IsFiltered], 1 )
I attempted to have it reference the data in the column to the below, but now Excel is telling I have too few arguments.
=COUNTIFS(COUNTIF(A$16:A$4202,"*8*"), Z$16:Z$4202, 1 )
How do I correct this?
It is
=COUNTIFS( A$16:A$4202,"*8*", Table[IsFiltered], 1 )or
=COUNTIFS( A$16:A$4202,"*8*", Z$16:Z$4202, 1 )
Thanks for responding to my inquiry. Wish there was a way to upload my template for other users to play with.
Column A is "Employee Level (1-8)". Formula in F3 should be counting rows where Employee Level = 8, and Column E ("A Rating") is = EP (Excellent Performance). This is being divided by the total number of Employees at Level 8 (F12), so C3 cell shows % of Level 8 employees with an EP Rating. There are a total of 14 employees at Level 8 in this data set.
Per your suggestion, I've added Column Z titled "IsFiltered" with your formula where the ID is the Employee Identification Number. I'm attempting to concentrate on just the numerator portion. I'm not sure I'm applying your suggestion correctly. Below is what I'm attempting to enter in the cell. Excel is saying there is something incorrect by highlighting the "Table[IsFiltered]" part of the formula.
=COUNTIFS(COUNTIF(A$16:A$4202,"*8*"), Table[IsFiltered], 1 )
I attempted to change it to reference Column Z (IsFiltered) by changing the formula to the below, but then I get the error message, "You've entered too few arguments for this function."
=COUNTIFS(COUNTIF(A$16:A$4202,"*8*"), Z$16:Z$4202, 1 )
Any suggestions?
try checking your dollar sign to know what exactly you locked , you rows or columns
