Formula to pick up data in a range of more than one calendar year

**julstaff** · 05-11-2015, 04:42 AM

Hello,

PLease, can somebody help me with the formula.
I have a list of jobs (A) with award date (B), start date (C) and finish date (D). I also have hours spent for each job (F) and I want to select which jobs I want to take into account (E).
I want a formula to sumproduct all jobs by calendar month depending on the month it was awarded and through to its finish date, then check if I want this job to be taken into account ("y" in (E) and multiply by hours (F).

The formula I have at the moment works perfectly well BUT only if the award dat, start date and finish date are in the same year.
When the award date is in a different year (2015) than the start date (2016), the formula wont work.

I am attaching the dummy spreadsheet with the existing formula.

THANK YOU!

**samba_ravi** · 05-11-2015, 05:01 AM

i7=SUMPRODUCT((DATE(YEAR($B$2:$B$4),MONTH($B$2:$B$4),0)<I$6)*(DATE(YEAR($D$2:$D$4),MONTH($D$2:$D$4)+1,0)>=I$6)*($E$2:$E$4="Y")*($F$2:$F$4))
try this and copy towards right

**julstaff** · 05-11-2015, 06:15 AM

THank you it works!

Formula to pick up data in a range of more than one calendar year

LinkBack

Thread Tools

Rate This Thread

Display

Formula to pick up data in a range of more than one calendar year

Re: Formula to pick up data in a range of more than one calendar year

Re: Formula to pick up data in a range of more than one calendar year

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Formula to Calculate the # of Calendar Months Surpassed Thus Far in a Year

[SOLVED] Need help with Calendar formula for previous year and next year

Year On Year Chart series created using named range with formula does not work.

Calculating totals from a date range on a rolling calendar year?

formula to calculate months in a specific year given a multi-year range

Bookmarks

Bookmarks

Posting Permissions