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

A6, C6, E6

--
Biff
Microsoft Excel MVP


"Mike" wrote:

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" wrote:

*Maybe* this array formula** :

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

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

** 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.


--
Biff
Microsoft Excel MVP


"Mike" wrote:

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)*24E6))

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