Search dates (split into weeks) and return value from another column

Morning all

Hoping someone can help - I have a worksheet which contains several columns of information. The two columns I am specifically interested in are;

Delivery Date
Sales Value
Material value
Labour value

What I want the formula to do is;

Search the date column for (as an example) dates between the 01/09/12 & 07/09/12, any results will then return a sum of (sales value - (material + labour value))

This does not all have to be in one cell i.e I would prefer for;

Dependant upon date within column P, the combined sales values are placed in cell L37, the combined materials values placed in M37, the combined labour values placed in N37


forecast (pe).xls

hopefully someone can help!

cheers

Hi

Can i do it without entering start/end dates in seperate cells? I am trying to reduce the requirement for end user input. Can the start/end dates be contained within the forumla itself?

Hello,

For example the following formula, for the second row:

Please Login or Register  to view this content.

Just drag down.
Attached you may find your book with the example.


EDIT: I thought it would always be from 01-09-2012 to 07-09-2012. Unfortunately will only work in that case.


Kind Regards,
FCarv

Hi

Thanks for that, out of interest, was is the "41154" value within the formula?

Ace_XL - it gets more complex in that case.

Week runs monday to sunday
The first week does not always start on the 1st
In the case of the completion date landing in the latter stage of a week, the sales etc values need to fall within the appropriate weeks forecast

Is there another foruma which will split the year into weeks? I could perhaps use that to define the start/end dates and refernece your previous forumla to it?

Originally Posted by akderitend

Hi

Thanks for that, out of interest, was is the "41154" value within the formula?

It's the valiue that corresponds to the 01-09-2012 data and "41159" corresponds to 07-09-2012.
You may check every single data value by using VALUE formula. Just try it out.

You may do like that if you want.
For example, if you want to search the entire month of august instead confirm the start day value (01-08-2012) and the end day value (30-08-2012). That would be 41122 and 41152 respectively.

And then change that parameter with that info in the formula. If it's applicable in your case and you have control of the file and attention not to drag through what you don't want to, you will be ok.

But still, is not a perfect solution.