It’s Friday so that means some more Formula Fun.  Today let’s look at calculating the percentage of cells in a range that contain a specific string.  I have used this to as an alternative to a Pivot Table to look at the percentage of customers that each of my companys Sales people represent.

It is a straightforward formula that searches in my list of customers and Sales people and returns how many customers each of those Sales people are responsible for.  This percentage then changes as we gain new customers or lose customers as the case may be.

It takes two formulas or functions to carry out this task COUNTIF and COUNTA. So here is my sample data. I have a list of customers and their sales persons that represent them.

I also have a small calculation area that will contain my formula and my results in Columns G and H.

The Formulas And Solution

The first part of the formula uses COUNTIF.  This is used to return the number of strings in my data range that contains my Sales Person ID that match the Sales Person ID in my calculation area in Column G.

The Syntax of COUNTIF is

=COUNTIF(range,criteria)

where

range – is the range of cells that you want to count based on your criteria

criteria – this is the criteria used to determine which cells to count

So in my example, COUNTIF returns the number of strings in the Sales Person ID column that match the list of Sales Person’s ID stored in Column G.

You can see this part of the formula below

=COUNTIF(Table1[[#All],[Sales Person ID]],G3)

I am using a table for my data storage in my Excel worksheet, so my formula uses a stuctured data formatting.  If you want to know more about using Excel Tables then you can read some more blog posts here and here .  I find the logic of the structured data very easy to work with.

So the first part of the formula will return how many times each Sales Person’s ID appears.  We can see the results below.

The second part of the formula will then calculate which percentage of the total customer base do my sales people represent.

So we now use COUNTA.  The Syntax of this formula is

COUNTA( argument1, [argument2, … argument_n] ), where each argument can be a range of cells, the formula can up to 255 arguments.

COUNTA will return the number of NON BLANK cells in my chosen range, so we can calculate how many strings there are in the our total List of Sales Person ID’s.  This allows us to the total number of each of the Sales Person’s ID’s to use in our formula.  My final formula looks like this-

=COUNTIF(Table1[[#All],[Sales Person ID]],G3)/COUNTA(Table1[Sales Person ID])

Just drag the formula down all of the Sales ID’s in Column H.  All that is needed is to format the cells as a percentage.

• Highlight cells
• Home Tab – Number Group
• Use the drop down arrow and select Percentage

That’s it.  Hope you enjoyed Formula Friday.

Dont forget to sign up to the Excel at Excel Newletter for 3 free Excel tips the first Wednesday of the month. Just click on the Sign Up Form to the right or use the link below.