Forum Discussion

Adam Blackshaw's avatar
Adam Blackshaw
Copper Contributor
May 01, 2018

Microsoft Excel 2013 - percentage completion rate using slicers

 

 

0down votehttps://superuser.com/questions/1318674/microsoft-excel-2013-percentage-completion-rate-using-slicers#

 

Hi need some help with a formula issue.

I have a list of engineering job packs- 1 Job pack can have several work points in this job pack. I have a status column and I want to count the percentage of "complete" cells In relation to the amount of work points filtered...

This is the formula I am using for the full table list but this does not update if I filter to a specific job pack or several job packs.

=COUNTIF([STATUS],"COMPLETE")/((COUNTA([STATUS]))+COUNTBLANK([STATUS]))

How can I edit or change this formula to update the percentage based on what is selected In the filter.

Formulas & Functions
training

1 Reply

Sort By
  • Matt Mickle's avatar
    Matt Mickle
    Bronze Contributor
    May 04, 2018

    Adam-

     

    If I understand you correct you want to see the % of complete items for a specific WP No based on the filter that you have on the data.  If this is your requirement then you can use this workbook to see how to calculate the % of the filtered items Completion Rate based on a specific WP No.


    The key is to use Sumproduct() and Subtotal() in conjunction. Subtotal has the ability to look at only filtered/visible rows.  In my example there are two formulas:

     

    Numerator:=SUMPRODUCT((A9:A30=$B$2)*(B9:B30="COMPLETE")*(SUBTOTAL(103,OFFSET(A9,ROW(A9:A30)-MIN(ROW(A9:A30)),0))))

     

    Denominator: =SUMPRODUCT((A9:A30=$B$2)*(SUBTOTAL(103,OFFSET(A9,ROW(A9:A30)-MIN(ROW(A9:A30)),0))))


    Then we use these two results in a simple formula: =B3/B4

     

    Here is an image of multiple results.  Hope this helps!:

Resources