Excel formula needed (not VBA)

Hi experts,

I have this list of dates in sheet1 and would require a formula in column B of the same sheet that:
Loops through a range of cells in Sheet2 and sums up Sheet2.Column.AC values for each occurrence in the Sheet2 Range where Sheet1.ColumnA.Date >= Sheet2.ColumnN AND Sheet1.ColumnA.Date <= Sheet2.ColumnS
Please note that neither ranges are ordered by date and they can’t be either.
VBA not an option at this point

For instance :

Sheet 1
+--------------------+--------------+
|           A           |         B      |
+--------------------+--------------+
|  10/15/2013   |                 |
+--------------------+--------------+
|  10/20/2013   |                 |
+--------------------+--------------+
|  10/08/2013   |                 |
+--------------------+--------------+

Sheet 2
+--------------------+--------------------+--------------------+
|           N           |            S          |           AC          |    
+--------------------+--------------------+--------------------+
|  10/02/2013   |   10/18/2013  |            2           |
+--------------------+--------------------+--------------------+
|  10/10/2013   |   10/30/2013  |            4           |
+--------------------+--------------------+--------------------+
|  10/05/2013   |   10/18/2013  |            6          |
+--------------------+--------------------+--------------------+

Final result of Sheet1 Column B should equal
+--------------------+--------------+
|           A           |         B      |
+--------------------+--------------+
|  10/15/2013   |        12     |
+--------------------+--------------+
|  10/20/2013   |         4      |
+--------------------+--------------+
|  10/08/2013   |         8      |
+--------------------+--------------+

Etc.

Thanks!
STIWasabiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

barry houdiniCommented:
You should be able to use a SUMIFS function for this (assuming Excel 2007 or later). Try this formula in Sheet1 B2 copied down

=SUMIFS(Sheet2!AC:AC,Sheet2!N:N,"<="&A2,Sheet2!S:S,">="&A2)

In earlier versions of Excel you can use SUMPRODUCT but you can't refer to whole columns so you'd need something like this assuming data in rows 2 to 100

=SUMPRODUCT(Sheet2!AC$2:AC$100,(Sheet2!N$2:N$100<=A2)*(Sheet2!S$2:S$100>=A2))

Adjust row numbers as required but they need to be consistent throughout the formula

regards, barry
0
STIWasabiAuthor Commented:
It works indeed for the simple example provided but once applied to my real data, it doesn't, so I guess I badly explained what I was trying to achieve. Any chance you could have a look at the attached (simple demo but with real data) and tell me what's wrong?

I would have thought the formula would output the following since Sheet2.ColumnG contains the number of hours per day between start and finish date...

So :
Oct. 15th --> 15
Oct. 16th --> 15
Oct. 17th --> 1
Oct. 18th --> 6
(see attached file, sheet 2, yellow colored area). As you'll see, the current formula outputs in Sheet1, zero (0) for both Oct. 16th and 17th, which is wrong.

Any ideas? We're almost there.

Thanks
DEMO.xlsx
0
barry houdiniCommented:
Your dates in column A in sheet1 also contain times - those times are skewing the results so you can either remove the times like this:

If you click in A1 you see this in formula bar 10/2/2013  20:00:00 or similar - you need to remove the 20:00:00

...or leave the data as it is and adjust the formula like this in B1 copied down

=SUMIFS(Sheet2!G:G,Sheet2!A:A,"<="&INT(A1),Sheet2!F:F,">="&INT(A1))

INT is used to extract the date only from the date/time value in A1

regards, barry
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
STIWasabiAuthor Commented:
Perfect, thanks !
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.