Formula that will adjust when I filter the spreadsheet

Question

I have this formula to calculate the percentage of times "yes" is in a cell but don't know hot to change the formula so that it will re-calculate the percentage when I filter the spreadsheet. This gives me a percentage for the entire company but I would like to filter by employee and it give me the percentage of times "yes" is in a cell for that particular employee.

=COUNTIF(BU2:BU367,"YES")/COUNTA(BU2:BU367)

mathetes · Answer

]YeatsN&nbsp;&nbsp;What you want to do is clear, but there's not enough info on the nature of the database where you want to do it. Is it possible to upload a sample that is representative of your actual database but without any real names?

yeatsn · Answer

mathetes&nbsp;&nbsp;Attached is a spreadsheet to test.&nbsp; In creating the test, some of the column letters changed but it's the same data.&nbsp; Thanks for the help.

sergeibaklan · Answer

YeatsN&nbsp;
The pattern is here&nbsp;https://exceljet.net/formula/count-visible-rows-only-with-criteria
Result is better to keep out of main table, when the formula could be
=SUMPRODUCT(
   (Table10[$600 Production Rate or Better Achieved]="Yes") *
   SUBTOTAL(103,
      OFFSET(
          Table10[[#Headers],[$600 Production Rate or Better Achieved]],
          ROW(Table10[$600 Production Rate or Better Achieved]) -
          ROW(Table10[[#Headers],[$600 Production Rate or Better Achieved]]),
     0)
   )
) / AGGREGATE(3,1,Table10[$600 Production Rate or Better Achieved])

sabry10 · Answer

=SUMIF(P7:Z7;&gt;=G11;P9:Z9) what is wrong here plz need to know

sergeibaklan · Answer

Sabry10&nbsp;
That's answered here&nbsp;https://techcommunity.microsoft.com/t5/resources-and-community/modern-excel-webinars/m-p/1136794/highlight/false#M591

Forum Discussion

Formula that will adjust when I filter the spreadsheet

5 Replies

Resources