SUMPRODUCT calculating difference between column values

[Mike]

The following formula works great when each row of columns G and H contain
values:

=SUMPRODUCT((Data!$D$2:$D$10000=E5)*(Data!$H$2:$H$10000>=A6)*(Data!$H$2:$H$10000<C6)*((Data!$H$2:$H$10000-Data!$G$2:$G$10000)*24>E6))

My problem is that column H rows do not always contain a value, and might
contain "". This is data from a database i can't control. How do i "trap"
these values.

As you can see from the formula, i'm not interested in the result from these
rows. The data in columns G and H are dates, by the way. I attempting to
calculate elapsed times, for rows where column D's value equals E5 and the
end time (in H) is equal or greater than A6 and less than C6, when BOTH start
and end times are present.

I've searched high and low. The experts on this forumn always have the
answer. Help please!

Mike

 

[T. Valko]

*Maybe* this array formula** :

Hard to test it when I don't know what the conditions of the variables are!

=SUM((D210000=E5)*(H2:H10000>=A6)*(H2:H10000<C6)*((IF(ISNUMBER(H2:H10000),H2:H10000,-1E+100)-G2:G10000)*24>E6))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[Mike]

This formula returned an unexpected 0. What is the '-1E+100' reference?

E5 will be a number, A6 and C6 are dates. The values in G and H are dates
or "" value. Is this variable infomation you needed?

Thanks for your help,
Mike

 

[T. Valko]

Give me 5 rows of sample data and the values of the variable cells:

A6, C6, E6

 

[Mike]

Clarification: I'm trying to count where the difference between the dates
and times is greater than the value in E6, a number.

 

[Mike]

Here you go...

D G H
1 LocationIdentifier StartDate EndDate
2 12 2/26/09 8:58 2/26/09 9:53
3 12 2/26/09 10:05 2/26/09 10:46
4 12 2/26/09 9:45 2/26/09 10:52
5 12 2/26/09 10:42 2/26/09 11:16
6 12 2/26/09 10:54 2/26/09 11:39

Again, thanks!


A6=2/26/09 9:00 AM (as a date)
C6=2/26/09 5:00 PM (as a date)
E5=12 (as a number)
E6=2 (as a number)

 

[Mike]

BTW, when I delete all of the non-date entries in column H my original
formula works just fine. But i need to analyze this data each day, and there
are thousands of records.

 

[Shane Devenshire]

Hi,

Try this array formula:

=SUMPRODUCT(--(Data!$D$2:$D$100=B9),--(Data!$H$2:$H$100>=B10),--(IF(ISNUMBER(Data!$H$2:$H$100),Data!$H$2:$H$100,0)<B11),--((((IF(ISNUMBER(Data!$H$2:$H$100),Data!$H$2:$H$100,0))-Data!$G$2:$G$100))*24>B12))

You will need to extend the range down as far as you data. Since this is an
array you will need to press Shift+Ctrl+Enter to enter it.

It is not at all clear why you are multiplying by 24, since you are looking
at dates, when you subtract two date that are 3 days apart, Excel return the
number 3, which you are multiplying by 24 for a difference of 72 days (not
hours).

 

[T. Valko]

OK, using your sample data and your *original* formula the result is 0 which
is correct.

Using this array formula** and your sample data the result I get is also 0
which is correct. I'm leaving out the sheet name and shortening the range
sizes.

=SUM((D210=E5)*(H2:H10>=A6)*(H2:H10<C6)*((IF(ISNUMBER(H2:H10),H2:H10)-G2:G10)*24>E6))

If I change your sample data slightly by:

Entering a formula blank (="") in H5
Changing E6 to 1

Then the array formula** correctly returns a result of 1. Row 4 meets all
the criteria.

If I change H2 to 2/26/2009 11:53 AM the array formula** correctly returns
2. Rows 2 and 4 meet all the criteria.

The formula I originally suggested also returns the correct results when
tested but now that I've seen some actual data the -1E100 expression is not
needed.

So, this array formula** does work in my tests using your sample data:

Just add the sheet name and expand the ranges to suit.

=SUM((D210=E5)*(H2:H10>=A6)*(H2:H10<C6)*((IF(ISNUMBER(H2:H10),H2:H10)-G2:G10)*24>E6))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.