Formula Help! Match, VLOOKUP and sum to date range

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

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
Joined
Jul 15, 2016
Messages
797
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Nov 15, 2018
Messages
6
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
Joined
Jul 15, 2016
Messages
797
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Nov 15, 2018
Messages
6
So, project resources in October should be $11,900 based on the attached images.
 

mattfitzy

New Member
Joined
Nov 15, 2018
Messages
6
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 ActualsOct-2018Nov-2018Dec-2018Jan-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 NameProject TaskResource CategoryRoleDaily Rate29-Oct30-Oct31-Oct1-Nov2-Nov
User 1 Project Resource $ 500.001.001.001.001.001.00
User 2 Project Resource $ 600.00
User 3 Project Resource $ 700.001.001.001.001.001.00
User 4 Project Resource $ 800.001.001.001.001.001.00
User 5 Project Resource $ 900.000.501.001.001.001.00
User 6 Project Resource $ 1,000.001.001.001.001.001.00
User 7 Project Resource $ 1,100.00
User 8 Project Resource $ 1,200.00
User 9Project Resource $ 1,300.000.50
User 10 Project Resource $ 1,400.00 1.001.00
User 11 Vendor Resource $ 1,500.00
User 12 Vendor Resource $ 1,600.00 1.001.00
User 13 Vendor Resource $ 1,700.00 0.500.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
Joined
Jul 15, 2016
Messages
797
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jul 15, 2016
Messages
797
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Nov 15, 2018
Messages
6
Thanks for sending this through, although I unfortunately couldn't get this formula to work. Any ideas?
 

Forum statistics

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

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
Top