Results 1 to 3 of 3

Thread: Percent of How Many within a Range, have a Sum Greater than 10

  1. #1

    Question Percent of How Many within a Range, have a Sum Greater than 10



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I'm dealing with miles driven to show a usage report. I have it broken up into three calculations on one worksheet per section that I'm reporting for.

    First Calculation is in J5 =sum(I5-H5) .simple difference I5 is the new miles, H5 is last months miles...dragged this formula through the range of J5:J20

    Second Calculation in K5 =IF(J5>10,"YES","NO") ..so I can see if the mileage is over 10 miles as required per month in a YES/NO Format...I then dragged this formula down the range of K5:K20

    Third Calculation is in K21 =SUM(COUNTIF(K5:K20,"Yes")/COUNTIF(K5:K20,"*")) ...basically, out of Row 5 thru 20, how many yes / total number of vehicles

    The result works that I get "56.25%" after formatting it as a percent.

    Is there a way I can make this into one formula that I can paste into each section/ worksheet?

    The end result just needs to show 56.25%, I really don't need YES and NO but for me it was easier when writing the formula.


    Let me know if you need a screen shot or more information. Thanks!!

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016
    Perhaps?

    =SUMPRODUCT(--(I5:I20-H5:H20>1))/COUNT(I5:I20)


  3. #3
    HAH! That worked beautifully! I'm amazed that the formula knows to acknowledge each equation I5 - H5, I6 - H6..etc. Thank you so much!

    All I had to do was change >1 to >10. Same result, 56.25
    Last edited by TAndress; 2017-03-22 at 06:01 PM.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •