Md. Abdur Rahim Rasel
A percentage is a number or ratio expressed as a fraction of 100. Today, in this tutorial, I will be showing how you can calculate the total percentage in Excel and will apply the SUM and the SUMIF function to make our calculation easier.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
5 Suitable Ways to Calculate Total Percentage in Excel
Let look at our datasheet that has been given in the screenshot. In our dataset, Some Product Names, their Order, and Delivery along with their Price have been given in Column B, Column C, Column D, and Column E respectively. Now, we calculate how many percentages of the Products have been delivered.
1. Calculate Total Percentage in Excel Between Two Column
First, we will calculate the percentage of delivering Apple. For this, Let’s follow the instructions:
Step 1:
- First, select cell F5 and type the formula in Formula Bar. the formula is:
=(D5/C5)
- Now, press Enter and you will get the ratio of products is 0.90
- After that, we will convert this ratio into a percentage. For this go to
Home → Number Ribbon → Percentage
- Now, our ratio turns into a percentage and it becomes 90%.
Step 2:
- After that, place your Cursor on the Bottom-Left on cell F5 and a Plus sign(+) pops up and press the Left-Click on your Mouse and drag it downward.
- Finally, you will be able to calculate the percentage between Column D and Column C.
Read More: Percentage Formula in Excel (6 Examples)
2. Find Out the Total Percentage in Excel Between Two Row
After calculating the percentage between Column D and Column C, now we will calculate the percentage according to Row. Let’s say, we will calculate the change of the Price of Product between March and April in terms of percentage. Let’s follow the steps:
Step 1:
- First of all, select cell D6.
- Now, type the formula of percentage in the Formula bar. The formula is:
=(C6-C5)/C6
- After typing the formula in the Formula Bar, press Enter on your keyboard and you will get the change of the Product’s Price -11.11% that means the price is decreasing.
Step 2:
- Similarly, you’ll get the change of Product’s Price in terms of percentage.
Related Content: Percentage Difference between Two Percentages Excel (2 Easy Ways)
3. Increase or Decrease of Value by Using the Total Percentage in Excel
In this method, we’ll calculate the Product’s Price change in February after ending January month. Let’s say, the price of Apple is $500.00 in January and $450.00 in February. To calculate the decreasing price of Apple in terms of percentage, follow the steps:
Step 1:
- First, select cell E5 and type the formula in the Formula Bar. The formula is
=(D5-C5)/D5
- After typing the formula in the Formula Bar, press Enter on your keyboard. After that, you will get the change of Apple’s price is -11.11%. A negative sign before the percentage means that the Apple price is decreasing.
Step 2:
- Now, place your Cursor on the Bottom-Left on Cell E5 and a Plus sign(+) pops up and press the Left-Click on your Mouse and drag it downward.
- After following these steps, you’ll get the total percentage of the products. In our last screenshot, a negative percentage means the price of products is decreasing otherwise the price is increasing.
Read More: How to Calculate Percentage Increase in Excel (Examples with All Criteria)
Similar Readings:
- How to Calculate Percentage in Excel Based on Cell Color (4 Methods)
- Calculate Percentage Change with Negative Numbers in Excel
- How to Calculate Revenue Growth Rate in Excel (3 Methods)
- Calculate Percentage in Excel VBA (Involving Macro, UDF, and UserForm)
- How to Add 20 Percent to a Price in Excel (2 Quick Methods)
4. Apply the SUM Function to Calculate Total Percentage in Excel
Now, we’ll calculate the percentage of Apple from the total order. For that, first, we determine the total order by using the SUM Function. Then we can easily get Apple’s order from the total order. Let’s follow the steps:
Step 1:
- First of all, type the SUM Function in cell C12. The SUM function is
=SUM(C5:C11)- Where Cell C5 to C11 is the order number of the product.
- After that, press Enter on your keyboard, and SUM Function returns the result and the result is 280 which is the sum of the order number in Cell C12.
Step 2:
- Now, go to cell E5 and type another formula in the Formula Bar. The formula will be in the Formula Bar
=C5/$C$12
- Look at the above formula, in the denominator we are using the Dollar sign($) which means cell C12 is an absolute reference.
- Again, press Enter on your keyboard, and you’ll get 86% as the percentage of Apple’s order from the total number of orders.
- Similarly, you can calculate the percentage of others products.
5. Use the SUMIF Function to Calculate Total Percentage in Excel
In the above method, we learn how to calculate percentages by using the SUM Function. Let us have several rows for the same product and we want to know what part of the total is made by all orders of that particular product. In this case, we can use the SUMIF function to calculate the percentage of the particular product. Let’s follow the Steps:
- First, select cell F5.
- In cell F5, type the SUMIF function. The SUMIF function is:
=SUMIF(B5:B11, F4, C5:C11)- After typing the formula, divide it by the total order number that has been calculated in the previous method.
- After that, press Enter on your keyboard, and the function returns the percentage of Apple and Apple’s order is 30.36% from all ordered products.
Things to Remember
👉 You can calculate percentages manually. For this type, the manual formula is in the Formula Bar.
👉 Apply the SUM function and SUMIF function to calculate the total percentage. The SUM function is, =SUM(C5:C11) and the SUMIF function is, =SUMIF(B5:B11, F4, C5:C11)
Conclusion
I hope all of the suitable methods mentioned above to calculate the total percentage will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to comment if you have any questions or queries.
I’m hoping you can help with an issue that I am having. I have Column D with a list of totals.The last cell in column D is D13 with the sum(D3:D12). Now I have column E for percent of total volume, this formula =D3/$D$13, =D4/$D$13,=D5/$D$13 and so on (D13 is where I have the Total for column D). To double check that my percent totals add up to 100, the last cell in E =sum(E3:E12) – this is the range that contains the percentages. It does add up to 100% but if I delete a value in column D, the total percent remains at 100% even though the percent for the individual value is now 0% (because I deleted the total for that item). Why isn’t my sum of percent cell updating? 🙁 I can’t figure this out.
Greetings Debbie,
First of all,we really appreciate for your question. Now if I am not afraid, you asked about why your total percentage value not changed despite deleting one sample value. If this is the case, the reason is pretty simple. as you delete one value, your total value also change. Which in turns also change the percentage value of all the other values. and this change will happen in a way that the total percentage values are always sums up to 100, following the basic arithmatic rule.
If this is not the case,please provide your Excel worksheet containing your data and we will have a look.
Thanks and Regards
Rubayed Razib