asked on

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!

barry houdini

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

STIWasabi

ASKER

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

ASKER CERTIFIED SOLUTION

barry houdini

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

STIWasabi

ASKER

Perfect, thanks !