# 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!
###### Who is Participating?

Commented:
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

Commented:
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

Author 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

Author Commented:
Perfect, thanks !
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.