Forum Discussion

YeatsN's avatar
YeatsN
Copper Contributor
Jan 29, 2020

Formula that will adjust when I filter the spreadsheet

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 g...
Formulas and Functions
YeatsN's avatar
YeatsN
Copper Contributor
Jan 29, 2020

mathetes 

 

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

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jan 29, 2020

YeatsN 

The pattern is here 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])

Resources