Formula to return % of opportunities won

TMittler

Hi,

I have a sales pipeline that we use stages for the opportunity. I am trying to come up with a formula that calculates what % of opportunities were won for each sales rep so I can include it on a dashboard. Any help is appreciated.

David Tutwiler

How is the data structured right now? Is there a column that designates the sales rep? You might be able to do this with a report directly without having to use formulas. Could you share a screenshot of some of the data, or at least describe the columns involved?

TMittler

Thanks for responding, Dave. I provided 3 screenshots below. The first is a sheet set up to aggregate the data I want for my dashboard to pull in. I have the labels on the left and the formulas on the right column. The second is the main table which has a sales rep field and a status field which are both dropdowns. The 3rd is the dashboard. I am looking for a formula next to each rep in the green box that calculates how many times the status is won vs any other status and displays it in a %. That way on our dashboard we can see each rep's close rate. Let me now if this makes sense.

David Tutwiler

I can't quite see those in enough detail to tell what is what as far as the column names go, but this is the general idea.

First, you need to number of opportunities that are for that salesperson whose Status is Won. That will be your numerator. This should look like:

=COUNTIFS({Salespeople}, Salesperson@row, {Status}, "Won")

Then you need the total number of opportunities for that Salesperson. This will be the denominator and be similar to the first formula:

=COUNTIF({Salespeople}, Salesperson@row)

Now just put the two together and you should get a percentage. It will come over as 0.35 and you will need to highlight the cell and select the % key in the editor toolbar to make it show up as a percentage, but it should get you going:

=COUNTIFS({Salespeople}, Salesperson@row, {Status}, "Won") / COUNTIF({Salespeople}, Salesperson@row)

*Please note that everything in curly brackets is a reference to the other sheet. You can name those references anything you want, but make sure they are referencing the correct columns.

TMittler

Thanks, David. I really appreciate it! Let me give it a shot. I'll let you know how I make out.