Forum Discussion
Using Filters to Auto-Update Complex Formulas
It is
=COUNTIFS( A$16:A$4202,"*8*", Table[IsFiltered], 1 )or
=COUNTIFS( A$16:A$4202,"*8*", Z$16:Z$4202, 1 )
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 )Sergei, thank you so much for the clarification. i was able to leverage your second option for my Excel spreadsheet and so far everything is looking good!
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
