Formula Help! Match, VLOOKUP and sum to date range

M

mattfitzy

New Member
Joined
Nov 15, 2018
Messages
6
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

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
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
 
Upvote 0
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
 
Upvote 0
Sorry Matt, are you able to copy/paste some data into the forum as many of us don't like clicking links these days.
 
Upvote 0
Tab One: I need to get the formulas under each month with the dollar calculation against Project or Vendor type from the next sheet.
[TABLE="width: 679"]
<colgroup><col><col><col span="4"></colgroup><tbody>[TR]
[TD]



[/TD]
[TD]Previous Actuals[/TD]
[TD]Oct-2018[/TD]
[TD]Nov-2018[/TD]
[TD]Dec-2018[/TD]
[TD]Jan-2019[/TD]
[/TR]
[TR]
[TD]Project Resource[/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[/TR]
[TR]
[TD]Vendor Resources[/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[/TR]
</tbody>[/TABLE]


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

[TABLE="width: 1234"]
<colgroup><col span="2"><col><col><col><col span="5"></colgroup><tbody>[TR]
[TD]Resource Name[/TD]
[TD]Project Task[/TD]
[TD]Resource Category[/TD]
[TD]Role[/TD]
[TD]Daily Rate[/TD]
[TD]29-Oct[/TD]
[TD]30-Oct[/TD]
[TD]31-Oct[/TD]
[TD]1-Nov[/TD]
[TD]2-Nov[/TD]
[/TR]
[TR]
[TD]User 1[/TD]
[TD] [/TD]
[TD]Project Resource[/TD]
[TD] [/TD]
[TD] $ 500.00[/TD]
[TD]1.00[/TD]
[TD]1.00[/TD]
[TD]1.00[/TD]
[TD]1.00[/TD]
[TD]1.00[/TD]
[/TR]
[TR]
[TD]User 2[/TD]
[TD] [/TD]
[TD]Project Resource[/TD]
[TD] [/TD]
[TD] $ 600.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]User 3[/TD]
[TD] [/TD]
[TD]Project Resource[/TD]
[TD] [/TD]
[TD] $ 700.00[/TD]
[TD]1.00[/TD]
[TD]1.00[/TD]
[TD]1.00[/TD]
[TD]1.00[/TD]
[TD]1.00[/TD]
[/TR]
[TR]
[TD]User 4[/TD]
[TD] [/TD]
[TD]Project Resource[/TD]
[TD] [/TD]
[TD] $ 800.00[/TD]
[TD]1.00[/TD]
[TD]1.00[/TD]
[TD]1.00[/TD]
[TD]1.00[/TD]
[TD]1.00[/TD]
[/TR]
[TR]
[TD]User 5[/TD]
[TD] [/TD]
[TD]Project Resource[/TD]
[TD] [/TD]
[TD] $ 900.00[/TD]
[TD]0.50[/TD]
[TD]1.00[/TD]
[TD]1.00[/TD]
[TD]1.00[/TD]
[TD]1.00[/TD]
[/TR]
[TR]
[TD]User 6[/TD]
[TD] [/TD]
[TD]Project Resource[/TD]
[TD] [/TD]
[TD] $ 1,000.00[/TD]
[TD]1.00[/TD]
[TD]1.00[/TD]
[TD]1.00[/TD]
[TD]1.00[/TD]
[TD]1.00[/TD]
[/TR]
[TR]
[TD]User 7[/TD]
[TD] [/TD]
[TD]Project Resource[/TD]
[TD] [/TD]
[TD] $ 1,100.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]User 8[/TD]
[TD] [/TD]
[TD]Project Resource[/TD]
[TD] [/TD]
[TD] $ 1,200.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]User 9[/TD]
[TD][/TD]
[TD]Project Resource[/TD]
[TD] [/TD]
[TD] $ 1,300.00[/TD]
[TD]0.50[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]User 10[/TD]
[TD] [/TD]
[TD]Project Resource[/TD]
[TD] [/TD]
[TD] $ 1,400.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1.00[/TD]
[TD]1.00[/TD]
[/TR]
[TR]
[TD]User 11[/TD]
[TD] [/TD]
[TD]Vendor Resource[/TD]
[TD] [/TD]
[TD] $ 1,500.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]User 12[/TD]
[TD] [/TD]
[TD]Vendor Resource[/TD]
[TD] [/TD]
[TD] $ 1,600.00[/TD]
[TD] [/TD]
[TD]1.00[/TD]
[TD]1.00[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]User 13[/TD]
[TD] [/TD]
[TD]Vendor Resource[/TD]
[TD] [/TD]
[TD] $ 1,700.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]0.50[/TD]
[TD]0.50[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]User 14[/TD]
[TD] [/TD]
[TD]Vendor Resource[/TD]
[TD] [/TD]
[TD] $ 1,800.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1.00[/TD]
[/TR]
[TR]
[TD]User 15[/TD]
[TD] [/TD]
[TD]Vendor Resource[/TD]
[TD] [/TD]
[TD] $ 1,900.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1.00[/TD]
[/TR]
[TR]
[TD]User 16[/TD]
[TD] [/TD]
[TD]Vendor Resource[/TD]
[TD] [/TD]
[TD] $ 2,000.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1.00[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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)
 
Upvote 0
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)
 
Upvote 0
You must log in or register to reply here.

Similar threads

U
  • Question Question
VLOOKUP that will SUM data based on Date Range
2
Replies
18
Views
1K
UnitedCloud01
U
M
  • Question Question
Excel Check Employee Rates
Replies
0
Views
1K
Megsnd
M
P
  • Question Question
Multiple a dynamic Range based on conditions
Replies
0
Views
878
Pepperoni
P
J
  • Question Question
Help modifying an existing macro to remove all of the "fixed" ranges in it
Replies
1
Views
691
mackc557
M
H
  • Question Question
Formula to calculate total values from a mix of progressive actual and forecast data
Replies
2
Views
947
heniek00
H

Forum statistics

Threads
1,224,817
Messages
6,181,149
Members
453,021
Latest member
Justyna P

Share this page

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