SUMPRODUCT

  • Thread starter Thread starter Pete
  • Start date Start date
P

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
 
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
 
Biff said:
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.
Click to expand...

That can be re-written as...

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