Formula Help! Match, VLOOKUP and sum to date range

[mattfitzy]

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

 

[RasGhul]

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]

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]

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]

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

 

[mattfitzy]

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]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		Previous Actuals	Oct-18	Nov-18	Dec-18	Jan-19	Feb-19	Mar-19	Apr-19	May-19	Jun-19	Jul-19	Aug-19	Sep-19
2	Project Resource		11,900	14,500
3	Vendor Resource		4,800
4	Total Planned
Sheet1

Cell Formulas
Range	Formula
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]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		Previous Actuals	Oct-18	Nov-18	Dec-18	Jan-19	Feb-19	Mar-19	Apr-19	May-19	Jun-19	Jul-19	Aug-19	Sep-19
2	Project Resource		11,900	14,500
3	Vendor Resource		4,800
4	Total Planned
Sheet1

Cell Formulas
Range	Formula
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]

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

 

[RasGhul]

Hi Matt,

It's working on mine did you change your resource names from post 8#?

Here's a copy of my mock up if this helps;

https://www.dropbox.com/s/8yudhikv7jfizhr/Project Resource sumproduct.xlsx?dl=0