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!!
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!!