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 do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

RasGhul

Well-known Member
Joined
Jul 15, 2016
Messages
612
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
612
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

ADVERTISEMENT

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
612

ADVERTISEMENT

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.

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="border-bottom: 1px solid black;background-color: #FCE4D6;;">Previous Actuals</td><td style="text-align: right;border-bottom: 1px solid black;color: #FFFFFF;background-color: #595959;;">Oct-18</td><td style="text-align: right;border-bottom: 1px solid black;color: #FFFFFF;background-color: #595959;;">Nov-18</td><td style="text-align: right;border-bottom: 1px solid black;color: #FFFFFF;background-color: #595959;;">Dec-18</td><td style="text-align: right;border-bottom: 1px solid black;color: #FFFFFF;background-color: #595959;;">Jan-19</td><td style="text-align: right;border-bottom: 1px solid black;color: #FFFFFF;background-color: #595959;;">Feb-19</td><td style="text-align: right;border-bottom: 1px solid black;color: #FFFFFF;background-color: #595959;;">Mar-19</td><td style="text-align: right;border-bottom: 1px solid black;color: #FFFFFF;background-color: #595959;;">Apr-19</td><td style="text-align: right;border-bottom: 1px solid black;color: #FFFFFF;background-color: #595959;;">May-19</td><td style="text-align: right;border-bottom: 1px solid black;color: #FFFFFF;background-color: #595959;;">Jun-19</td><td style="text-align: right;border-bottom: 1px solid black;color: #FFFFFF;background-color: #595959;;">Jul-19</td><td style="text-align: right;border-bottom: 1px solid black;color: #FFFFFF;background-color: #595959;;">Aug-19</td><td style="text-align: right;border-bottom: 1px solid black;color: #FFFFFF;background-color: #595959;;">Sep-19</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Project Resource</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">11,900</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">14,500</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Vendor Resource</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">4,800</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Total Planned</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D1</th><td style="text-align:left">=EOMONTH(<font color="Blue">C1,0</font>)+1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">(<font color="Red">Sheet2!$F$3:$M$18</font>)*(<font color="Red">Sheet2!$C$3:$C$18=$A2</font>)*(<font color="Red">Sheet2!$F$2:$M$2>=C$1</font>)*(<font color="Red">Sheet2!$F$2:$M$2<=EOMONTH(<font color="Green">C$1,0</font>)</font>)*Sheet2!$E$3:$E$18</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C3</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">(<font color="Red">Sheet2!$F$3:$M$18</font>)*(<font color="Red">Sheet2!$C$3:$C$18=$A3</font>)*(<font color="Red">Sheet2!$F$2:$M$2>=C$1</font>)*(<font color="Red">Sheet2!$F$2:$M$2<=EOMONTH(<font color="Green">C$1,0</font>)</font>)*Sheet2!$E$3:$E$18</font>)</td></tr></tbody></table></td></tr></table><br />
 

RasGhul

Well-known Member
Joined
Jul 15, 2016
Messages
612
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;

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="border-bottom: 1px solid black;background-color: #FCE4D6;;">Previous Actuals</td><td style="text-align: right;border-bottom: 1px solid black;color: #FFFFFF;background-color: #595959;;">Oct-18</td><td style="text-align: right;border-bottom: 1px solid black;color: #FFFFFF;background-color: #595959;;">Nov-18</td><td style="text-align: right;border-bottom: 1px solid black;color: #FFFFFF;background-color: #595959;;">Dec-18</td><td style="text-align: right;border-bottom: 1px solid black;color: #FFFFFF;background-color: #595959;;">Jan-19</td><td style="text-align: right;border-bottom: 1px solid black;color: #FFFFFF;background-color: #595959;;">Feb-19</td><td style="text-align: right;border-bottom: 1px solid black;color: #FFFFFF;background-color: #595959;;">Mar-19</td><td style="text-align: right;border-bottom: 1px solid black;color: #FFFFFF;background-color: #595959;;">Apr-19</td><td style="text-align: right;border-bottom: 1px solid black;color: #FFFFFF;background-color: #595959;;">May-19</td><td style="text-align: right;border-bottom: 1px solid black;color: #FFFFFF;background-color: #595959;;">Jun-19</td><td style="text-align: right;border-bottom: 1px solid black;color: #FFFFFF;background-color: #595959;;">Jul-19</td><td style="text-align: right;border-bottom: 1px solid black;color: #FFFFFF;background-color: #595959;;">Aug-19</td><td style="text-align: right;border-bottom: 1px solid black;color: #FFFFFF;background-color: #595959;;">Sep-19</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Project Resource</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">11,900</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">14,500</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Vendor Resource</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">4,800</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Total Planned</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D1</th><td style="text-align:left">=EOMONTH(<font color="Blue">C1,0</font>)+1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">(<font color="Red">Sheet2!$F$3:$M$18</font>)*(<font color="Red">Sheet2!$C$3:$C$18=$A2</font>)*(<font color="Red">Sheet2!$F$2:$M$2>=C$1</font>)*(<font color="Red">Sheet2!$F$2:$M$2<=EOMONTH(<font color="Green">C$1,0</font>)</font>)*Sheet2!$E$3:$E$18</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C3</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">(<font color="Red">Sheet2!$F$3:$M$18</font>)*(<font color="Red">Sheet2!$C$3:$C$18=$A3</font>)*(<font color="Red">Sheet2!$F$2:$M$2>=C$1</font>)*(<font color="Red">Sheet2!$F$2:$M$2<=EOMONTH(<font color="Green">C$1,0</font>)</font>)*Sheet2!$E$3:$E$18</font>)</td></tr></tbody></table></td></tr></table><br />
 

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,136,328
Messages
5,675,136
Members
419,551
Latest member
thangxpm

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