insertcleverid
New Member
- Joined
- Jan 9, 2017
- Messages
- 2
I want to report to my sales staff (all non-excel users) how they are progressing towards their yearly production goals. Each week we collect the number of Calls, Contacts, Appointments and Sales (our conversion milestones) and record them in the chart below (A15:F24). From this practice we have figured out how many of each conversion milestone you need on average to get a single sale, and from there its easy to see how many of each conversion milestone you need each week to get X sales in a year (A4:E7).
What I don't have is a formula that will fetch a sum of all weekly reported figures from the data chart (A15:F24) and output it to B12:E12 which I have highlighted in yellow.
I tried VLOOKUP but it only does sums multiple columns as far as I can tell. I believe that an INDEX/MATCH formula needs to be couched in a SUM formula and I can't get that to work. I've also looked at SUMPRODUCT but can't find any examples of how it would help here.
If you need more information that this, here's what's happening in the spreadsheet: It needs to be extremely simple so they can and will use it, so it needs to work from the minimum # of clicks. Here's what I have done so far. First you select an agent's name in the drop-down Data Validation box (A2) that will then populate cell B2 with an IFS formula referencing the goal chart (G4:H7). Then you plug in the number of weeks we're into the season (D2). (Later at some point I might use an index match formula to auto-populate this) This is multiplied by the yearly goal, then multiplied by the Weekly Goals To Get There values from the chart B7:E7 for each milestone. This outputs to Needed (A11:E11) the number of each milestone you would expect to have at this point if you were on track.
Thanks in advance.
<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>
<colgroup><col style="width: 36px"><col width="100"><col width="61"><col width="61"><col width="61"><col width="61"><col width="61"><col width="59"><col width="67"></colgroup><tbody>
</tbody>
What I don't have is a formula that will fetch a sum of all weekly reported figures from the data chart (A15:F24) and output it to B12:E12 which I have highlighted in yellow.
I tried VLOOKUP but it only does sums multiple columns as far as I can tell. I believe that an INDEX/MATCH formula needs to be couched in a SUM formula and I can't get that to work. I've also looked at SUMPRODUCT but can't find any examples of how it would help here.
If you need more information that this, here's what's happening in the spreadsheet: It needs to be extremely simple so they can and will use it, so it needs to work from the minimum # of clicks. Here's what I have done so far. First you select an agent's name in the drop-down Data Validation box (A2) that will then populate cell B2 with an IFS formula referencing the goal chart (G4:H7). Then you plug in the number of weeks we're into the season (D2). (Later at some point I might use an index match formula to auto-populate this) This is multiplied by the yearly goal, then multiplied by the Weekly Goals To Get There values from the chart B7:E7 for each milestone. This outputs to Needed (A11:E11) the number of each milestone you would expect to have at this point if you were on track.
Thanks in advance.
<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>
A | B | C | D | E | F | G | H | |
1 | Agent | Year Goal | Number of Weeks | |||||
2 | Bob | 100 | 8 | |||||
3 | ||||||||
4 | Yearly Sales | Weekly Goals to Get There | Agent | Year Goal | ||||
5 | Calls | Contacts | Appts | Sales | Jim | 120 | ||
6 | 100 | 80.07 | 18.58 | 2.65 | 1.92 | Bob | 100 | |
7 | 1 | 0.8 | 0.19 | 0.03 | 0.02 | Frank | 80 | |
8 | ||||||||
9 | Calls | Contacts | Appts | Sales | ||||
10 | Weekly Goals | 80.00 | 19.00 | 3.00 | 2.00 | |||
11 | Needed | 640.00 | 152.00 | 24.00 | 16.00 | |||
12 | Made | |||||||
13 | Gap | |||||||
14 | ||||||||
15 | Date | Agent | Calls | Contacts | Appt | Sales | ||
16 | 12/20/2016 | Jim | 153 | 39 | 5 | 2 | ||
17 | 12/20/2016 | Bob | 60 | 25 | 3 | 3 | ||
18 | 12/20/2016 | Frank | 27 | 5 | 0 | 0 | ||
19 | 12/27/2016 | Jim | 115 | 20 | 2 | 2 | ||
20 | 12/27/2016 | Bob | 80 | 19 | 5 | 3 | ||
21 | 12/27/2016 | Frank | 70 | 22 | 3 | 2 | ||
22 | 1/3/2017 | Jim | 102 | 21 | 2 | 2 | ||
23 | 1/3/2017 | Bob | 88 | 13 | 3 | 2 | ||
24 | 1/3/2017 | Frank | 59 | 11 | 2 | 2 |
<colgroup><col style="width: 36px"><col width="100"><col width="61"><col width="61"><col width="61"><col width="61"><col width="61"><col width="59"><col width="67"></colgroup><tbody>
</tbody>