SUMPRODUCT

[Pete]

I use the following formula to sum the amount of downtime
incurred between two times from column A & column B the
time lost is in column E

=SUMPRODUCT(--($A$12:$A$19>=A5),--($B$12:$B$19<=B5),--
($E$12:$E$19))

what I would like to do is sum any downtime (col E)
incurred between the above times (col A & col B) if
Z12:Z19 equals any of the values (text) from
PlannedDownTime (named range)

hope this makes sense

Pete

 

[Biff]

Hi!

You can't use a named range in the context of OR like that.

You would need to list the individual conditions like this:

=SUMPRODUCT((A12:A19>=A5)*(B12:B19<=B5)*(Z12:Z19=
{"A","B","C"})*E12:E19)

Using the double unary in the above formula results in a
#VALUE! error so the need for multiplication of the arrays.

Biff

 

[Aladin Akyurek]

Hi!

You can't use a named range in the context of OR like that.

You would need to list the individual conditions like this:

=SUMPRODUCT((A12:A19>=A5)*(B12:B19<=B5)*(Z12:Z19=
{"A","B","C"})*E12:E19)

Using the double unary in the above formula results in a
#VALUE! error so the need for multiplication of the arrays.

That can be re-written as...

=SUMPRODUCT(--(A12:A19>=A5),--(B12:B19<=B5),--ISNUMBER(MATCH(Z12:Z19,
{"A","B","C"},0)),E12:E19)