I just cant seem to get this to work. Basically I need to create an if array that has logic but also multiple occurrences. the easy way is in using the formula in cells C2:C6 and the SUM in E2, but this will be a very dynamic sheet with varying amounts of data and the data in A and B is from a plugin that has weird formulas in the cells and doesn't really work well with auto fill. An index match would work (maybe 
) if only I could get it to produce an array (the failed attempt in D2), FILTER or JOINTEXT would work but this is for excel 2016.
In short I'm adding up the time the system is in the on state over a period of time, the period of time is controlled by the plugin so I don't need to worry about that. However I need it to be a single formula in one cell.
) if only I could get it to produce an array (the failed attempt in D2), FILTER or JOINTEXT would work but this is for excel 2016.In short I'm adding up the time the system is in the on state over a period of time, the period of time is controlled by the plugin so I don't need to worry about that. However I need it to be a single formula in one cell.
| Example.xlsx | |||||||
|---|---|---|---|---|---|---|---|
| A | B | C | D | E | |||
| 1 | 44608 | ON | |||||
| 2 | 44608.01 | OFF | 0.004919 | 0.014756 | 0.014594 | ||
| 3 | 44608.01 | ON | 0 | ||||
| 4 | 44608.02 | OFF | 0.005 | ||||
| 5 | 44608.02 | ON | 0 | ||||
| 6 | 44608.02 | OFF | 0.004676 | ||||
Sheet1 | |||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| D2 | D2 | =SUM(IF(B1:B6="off",INDEX(A1:A6,MATCH("OFF",B1:B6,0),1)-INDEX(A1:A6,MATCH("OFF",B1:B6,0)-1,1),0)) |
| E2 | E2 | =SUM(C2:C6) |
| C2:C6 | C2 | =IF(B2="off",A2-A1,0) |
| Press CTRL+SHIFT+ENTER to enter array formulas. | ||
