Complicated VLOOKUP or INDEX MATCH formula for simple output

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>
ABCDEFGH
1AgentYear GoalNumber of Weeks
2Bob1008
3
4Yearly SalesWeekly Goals to Get ThereAgentYear Goal
5CallsContactsApptsSalesJim120
610080.0718.582.651.92Bob100
710.80.190.030.02Frank80
8
9CallsContactsApptsSales
10Weekly Goals80.0019.003.002.00
11Needed640.00152.0024.0016.00
12Made
13Gap
14
15DateAgentCallsContactsApptSales
1612/20/2016Jim1533952
1712/20/2016Bob602533
1812/20/2016Frank27500
1912/27/2016Jim1152022
2012/27/2016Bob801953
2112/27/2016Frank702232
221/3/2017Jim1022122
231/3/2017Bob881332
241/3/2017Frank591122

<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>
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Hi Clever
you could use in B12 =SUMIF($B$16:$B$24,$A$2.C16:C24)
and copy the formula to C12, D12 and E12
I got
22857118

<tbody>
</tbody>
Cheers
Sergio
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,972
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top