Home |
Search |
Today's Posts |
#1
|
|||
|
|||
SUMPRODUCT
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 |
#2
|
|||
|
|||
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 -----Original Message----- 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 . |
#3
|
|||
|
|||
Biff wrote:
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I reference =, <, or > sign in SUMPRODUCT | Excel Discussion (Misc queries) | |||
Another Sumproduct & #N/A problem | Excel Worksheet Functions | |||
Sumproduct ... Empty Cells vs Spaces? | Excel Discussion (Misc queries) | |||
Sumproduct function not working | Excel Worksheet Functions | |||
adding two sumproduct formulas together | Excel Worksheet Functions |