How to Calculate Percentage of Completion in Excel (3 Methods)

Get FREE Advanced Excel Exercises with Solutions!

While working in Microsoft Excel, you might need to calculate the percentages of a project’s completion so that your final report looks impressive. This often creates difficulties while calculating. But no more from today. In this article, I am sharing with you how to calculate the percentage of completion in Excel.


How to Calculate Percentage of Completion in Excel: 3 Quick Methods

In the following, I have explained 3 quick and simple tricks to calculate the percentage of completion in Excel. Stay tuned!

Suppose we have a dataset with some Dates, Tasks needed to be completed, and their Status explaining whether are those completed or not. Now we will calculate the percentage of completion from this table.


1. Use COUNTA Function to Calculate Percentage of Completion

If you are looking for a simple solution to calculate the percentage of completion in a quick way then you are at the right place. Here, I am gonna show you a simple way to apply the COUNTA function. The COUNTA function will count blank cells in a range in this list.

Steps:

  • First, select a cell (F5) and apply the following formula down-
=COUNTA(D5:D12)/COUNTA(C5:C12)

Use COUNTA Function to Calculate Percentage of Completion in Excel

Here, COUNTA(D5:D12) counts the number of non-blank cells (the string Complete in this case) and similarly, COUNTA(C5:C12) counts the non-blank cells (total number of Tasks). Then the earlier result will be divided by the later output to produce the final result.

  • Next, hit ENTER.
  • Finally, we have successfully calculated the percentage of completion in our Excel worksheet.

Use COUNTA Function to Calculate Percentage of Completion in Excel


2. Manual Formula to Calculate Percentage of Completion

Often we face difficulties making a report where we have to calculate the percentage of completion of tasks, students obtained marks, etc. It won’t be a problem anymore from now. In this method, I will show you an easy trick to calculate the percentage of completion with a basic mathematics equation.

Suppose we have a dataset of some Students’ Names, Their Obtained Marks in multiple subjects, and their Total Marks. Now we will calculate the percentage of these students who obtained marks.

Steps:

  • Presently, we will determine the total obtained mark of each student. To do that, select a cell (G8) and write the following formula down-
=C8+D8+E8+F8

  • Gently, press ENTER and drag the “Fill Handle” down to fill all the cells.

  • Next, choose another cell (H8) and apply the below formula-
=G8/$C$4

Manual Formula to Calculate Percentage of Completion

  • Similarly, press ENTER and pull the “Fill Handle” down. Make sure to apply the absolute cell reference to the cell which needs to remain consistent.
  • In summary, our desired output calculating the percentage of each student obtained mark will be in our hands. Here, you can see the percentage of total marks each student has achieved.

Manual Formula to Calculate Percentage of Completion


3. Combine COUNTIF and COUNTA Functions

With the combination of the COUNTIF and COUNTA functions, you can calculate the percentage of completion too. Just follow the instructions below-

Steps:

  • To start with, choose a cell (F5) and apply the formula below-
=COUNTIF(D5:D12,"Complete")/COUNTA(C5:C12)

Where,

  • The COUNTIF function will count cells with the given condition- “Complete” in the argument.
  • The COUNTA function will count cells containing any type of information in this range.

Then the earlier result (result from COUNTIF) will be divided by the later output (result from COUNTA) to produce the final result.

Combine COUNTIF and COUNTA Functions to Calculate Percentage of Completion

  • Click ENTER.
  • In conclusion, we have successfully calculated the percentage of completion in Excel within a blink of an eye.

How to Calculate Percentage of Completion in Excel

Comparing this method with the first method (method 1), the main difference here is we can define the specific word (Complete) with the help of the COUNTIF function, on the other hand, the COUNTA function calculates with non-blank cells only.

Read More: How to Calculate Remaining Shelf Life Percentage in Excel


Things to Remember

  • Sometimes you might get decimal values instead of percentages. No worries! Selecting cells click the “Percent Style” from the Home ribbon to get the percentage output.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

In this article, I have tried to cover all the methods to calculate the percentage of completion in Excel. Take a tour of the practice workbook and download the file to practice by yourself. I hope you find it helpful. Please inform us in the comment section about your experience.


Related Articles


<< Go Back to Percentage Formula ExamplesCalculating Percentages | Calculate in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Wasim Akram
Wasim Akram

Wasim Akram holds a BSc degree in Industrial and Production Engineering from Ahsanullah University of Science and Technology. Over the past 2 years, he has been actively contributing to the ExcelDemy project, where he has authored more than 150 articles. Now, he is working as an Excel VBA and Content Developer. He likes learning new things about Microsoft Office, especially Excel VBA, Power Query, Data Analysis, and Excel Statistics. He is also very interested in machine learning and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo