Let's say you own a grocery store. You sell different items. As you know, due to the corona virus pandemic the nature of purchases has changed a lot. So you get the data of sales and now you want a percentage breakdown of each order of each item so that you can analyse what you need more for your customers.
If you want to do such a percentage breakdown with counts of items in Excel, you can use the generic formula below:
Generic Formula
| =COUNTIF(Items, item)/COUNTA(Items) |
Items: The range that contains the item name in the order list.
Item: The item of which you want to get the percentage.
Let’s see an example to make things clear.
Here we are taking only the concerned part of the order table. We have items of order mentioned in range C4:C27. We have named this range as Items.
We have another table that has unique names of items from the list. We need to get the count of these items in the order list and then the percentage of these items.
To get the unique list of items, you can copy paste the items from the table and then remove duplicates. You can use the formula too. If you are using Excel 2016 or older then you can use this formula to remove duplicates. If you are okay with VBA then this formula is the simplest way to remove duplicates in Excel 2106 or older. If you are on Excel 2019 or 365, use the UNIQUE function.
To get the count of each item in the list we use the COUNTIF function.
| =COUNTIF(Items,F4) |
Now to get the percentage breakdown of the items, we use the generic formula mentioned in the beginning.
| =COUNTIF(Items, F4)/COUNTA(Items) |
You can see that we have some decimal points in the Order Percentage column. To convert them into percentages we need to convert the cell formatting to percentages. Simply use the CTRL+SHIFT+% to convert selected cells into percentages.
Now you can see what percentage of orders are placed for each item by a customer or whole.
How does it work?
It is simple. The COUNTIF function gets the count of the items in the list. And the COUNTA function gets the total count of orders. Finally we divide the count of items by the total number of orders which gives us the percentage breakdown of items.
This is how the formula is solved:
| =COUNTIF(Items, F4)/COUNTA(Items) |
| =5/24 |
| =21% |
So yeah guys, this is how you can get the summary with percentage breakdown of items in Excel. I hope it was explanatory and served your purpose. If you have any questions or it doesn't solve your problem, let me know in the comments section below. Till then keep learning and keep Excelling.
Related Articles:
Calculate Percentage of Total in Excel | Calculating percentages is a basic task in any field of work. We always want to know how much work is done. In school/collage we always calculated our attendance percentage carefully to be able to appear in the exam.
How to Calculate Percentage Discount in Excel To calculate change in percentage discount of items, we simply subtract the discount price from total price and then get the percentage. This simple excel formula will get this done.
How to Increase by percentage in Excel | To increase a given value by a given percentage we can simply add the percentage of increment in 100% and then multiply the actual number with it. This will increase the given value with the given percentage.
How to Calculate Profit margin percentage in Excel | To calculate the profit margin we simply divide the sales price by purchase price and subtract the result from 1. This gives us the percentage of profit margin.
Mathematical Percentage Decrease Change in Excel | In this article, how to use percentage decrease change formula in Excel 2016.We need to find the percentage decrease in value due to decrement percentage change.\
How to Calculate Project Complete Percentage in Excel | When each task is completed, we say that project is completed 100%. In that case we would like to monitor how the task is completed while we work on the project. In this article, we will learn how you can calculate the project complete percentage in Excel
Get the percentage for the sold product in Excel | In this article, we will learn about how to calculate the percent for the sold product in excel. Get the Percent for the sold product data in excel using a simple formula. Divide the sold product amount by the total amount of the product.
Find Total Amount From Percentage | for instance, working with percentage values in excel sometimes we need to get the total amount from the given percentage and the number. Given a discounted amount and discount percent, get the amount before discount or the total amount.
Popular Articles:
50 Excel Shortcuts to Increase Your Productivity | Get faster at your task. These 50 shortcuts will make you work even faster on Excel.
How to use Excel VLOOKUP Function| This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.
How to use the Excel COUNTIF Function| Count values with conditions using this amazing function. You don't need to filter your data to count specific values. Countif function is essential to prepare your dashboard.
How to Use SUMIF Function in Excel | This is another dashboard essential function. This helps you sum up values on specific conditions.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
