# Formula Help! Match, VLOOKUP and sum to date range

#### mattfitzy

##### New Member
Hi There,

I'm looking for some assistance with a formula I've been stewing over for a couple of days now and would appreciate some assistance.

I am putting together a spreadsheet to help with forecasting where I have an input template that will feed a formulated forecasting vs. actual calc, and I need to calculate a rate for the month (by a category) multiplied by a dollar value.

Tab one will be known as my forecast spreadsheet, and I will explain below. This is where I need to enter a formula to calculate total cost for the month, by category, being 'Project Resource' or 'Vendor Resource'

Cell A6: "Project Resource"
Cell A7: "Vendor Resource"

Cell C5: Cell CN: Months (Oct-2018, Nov-2018, Dec-2018 etc.)

The formula to calculate a total dollar figure will be sitting in C6:N6 (against project resource) and vice verser for Vendor Resources in C7:N7.

Tab two is my data input template. It is a 'calendar' type worksheet showing each person working on the project, the forecasted days they will work into the future, their rate and if they are a project or vendor resource. I need to look up this data, sum their days worked in the month, categorise them by vendor or project and multiply by their rate.

A7:A29 is the Employee on the project
C7:C29 is if they are a project or vendor resource
E7:E29 is the employee's rate \$
F7:F29 is day one on the project, G7:G29 is day two, etc. forecasted to Cell NQ7:NQ29 (each column is one day, and not all resources are forecasted to work each day)

To summarise, I need total forecasted cost of each resource type (project or vendor) by month, using a daily calendar type input.

Hopefully I have explained this well enough. It's a bit complicated.

Thanks,
Matt

### Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

#### RasGhul

##### Well-known Member
Hi Matt,

You can do sumifs with date criteria but it would be easier to work with some sample data if you could post.

Ras

#### mattfitzy

##### New Member
Please find attached tab one here - you will see where I need to put the formulas

My input template (tab two as referred above) can be found here

The input template is a 'calendar' type input sheet to forecast, I need to forecast by month, multiplied by each users rate for the days they are planned to be working in that month, and separate the costs by project or vendor.

Thanks,
Matt

#### RasGhul

##### Well-known Member
Sorry Matt, are you able to copy/paste some data into the forum as many of us don't like clicking links these days.

#### mattfitzy

##### New Member
So, project resources in October should be \$11,900 based on the attached images.

#### mattfitzy

##### New Member
Tab One: I need to get the formulas under each month with the dollar calculation against Project or Vendor type from the next sheet.
 Previous Actuals Oct-2018 Nov-2018 Dec-2018 Jan-2019 Project Resource \$ - \$ - \$ - \$ - \$ - Vendor Resources \$ - \$ - \$ - \$ - \$ -

<colgroup><col><col><col span="4"></colgroup><tbody>
</tbody>

Tab Two (input sheet) - This is where I need to calculate planned days against each users daily rate and group by month if they're a project or vendor resource into the first tab

 Resource Name Project Task Resource Category Role Daily Rate 29-Oct 30-Oct 31-Oct 1-Nov 2-Nov User 1 Project Resource \$ 500.00 1.00 1.00 1.00 1.00 1.00 User 2 Project Resource \$ 600.00 User 3 Project Resource \$ 700.00 1.00 1.00 1.00 1.00 1.00 User 4 Project Resource \$ 800.00 1.00 1.00 1.00 1.00 1.00 User 5 Project Resource \$ 900.00 0.50 1.00 1.00 1.00 1.00 User 6 Project Resource \$ 1,000.00 1.00 1.00 1.00 1.00 1.00 User 7 Project Resource \$ 1,100.00 User 8 Project Resource \$ 1,200.00 User 9 Project Resource \$ 1,300.00 0.50 User 10 Project Resource \$ 1,400.00 1.00 1.00 User 11 Vendor Resource \$ 1,500.00 User 12 Vendor Resource \$ 1,600.00 1.00 1.00 User 13 Vendor Resource \$ 1,700.00 0.50 0.50 User 14 Vendor Resource \$ 1,800.00 1.00 User 15 Vendor Resource \$ 1,900.00 1.00 User 16 Vendor Resource \$ 2,000.00 1.00

<colgroup><col span="2"><col><col><col><col span="5"></colgroup><tbody>
</tbody>

#### RasGhul

##### Well-known Member
Hi Matt,

You could use the following, be careful to change the Vendor Resources (Sheet1) to "Vendor Resource" from what you're matching on Sheet2;

The Header Dates should be the 1st of each month just formatted as "Oct-18"etc.

Book1
ABCDEFGHIJKLMN
1Previous ActualsOct-18Nov-18Dec-18Jan-19Feb-19Mar-19Apr-19May-19Jun-19Jul-19Aug-19Sep-19
2Project Resource11,90014,500
3Vendor Resource4,800
4Total Planned
Sheet1
Cell Formulas
RangeFormula
D1=EOMONTH(C1,0)+1
C2=SUMPRODUCT((Sheet2!\$F\$3:\$M\$18)*(Sheet2!\$C\$3:\$C\$18=\$A2)*(Sheet2!\$F\$2:\$M\$2>=C\$1)*(Sheet2!\$F\$2:\$M\$2<=EOMONTH(C\$1,0))*Sheet2!\$E\$3:\$E\$18)
C3=SUMPRODUCT((Sheet2!\$F\$3:\$M\$18)*(Sheet2!\$C\$3:\$C\$18=\$A3)*(Sheet2!\$F\$2:\$M\$2>=C\$1)*(Sheet2!\$F\$2:\$M\$2<=EOMONTH(C\$1,0))*Sheet2!\$E\$3:\$E\$18)

#### RasGhul

##### Well-known Member
Hi Matt,

You could use the following, be careful to change the Vendor Resources (Sheet1) to "Vendor Resource" from what you're matching on Sheet2;

The Header Dates should be the 1st of each month just formatted as "Oct-18"etc. Disregard the"4,800" for Vendor Resource" I was just testing the formula against the other name;

Book1
ABCDEFGHIJKLMN
1Previous ActualsOct-18Nov-18Dec-18Jan-19Feb-19Mar-19Apr-19May-19Jun-19Jul-19Aug-19Sep-19
2Project Resource11,90014,500
3Vendor Resource4,800
4Total Planned
Sheet1
Cell Formulas
RangeFormula
D1=EOMONTH(C1,0)+1
C2=SUMPRODUCT((Sheet2!\$F\$3:\$M\$18)*(Sheet2!\$C\$3:\$C\$18=\$A2)*(Sheet2!\$F\$2:\$M\$2>=C\$1)*(Sheet2!\$F\$2:\$M\$2<=EOMONTH(C\$1,0))*Sheet2!\$E\$3:\$E\$18)
C3=SUMPRODUCT((Sheet2!\$F\$3:\$M\$18)*(Sheet2!\$C\$3:\$C\$18=\$A3)*(Sheet2!\$F\$2:\$M\$2>=C\$1)*(Sheet2!\$F\$2:\$M\$2<=EOMONTH(C\$1,0))*Sheet2!\$E\$3:\$E\$18)

#### mattfitzy

##### New Member
Thanks for sending this through, although I unfortunately couldn't get this formula to work. Any ideas?

Replies
3
Views
169
Replies
0
Views
238
Replies
2
Views
563
Replies
1
Views
324
Replies
1
Views
664

1,181,785
Messages
5,932,036
Members
436,815
Latest member

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

### Which adblocker are you using?

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

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