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

1. ## Percent of How Many within a Range, have a Sum Greater than 10

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. Perhaps?

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

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