Array or Vector? sum the max of two cells in a row, when row meets certain criteria

[Jocamo]

Screen Shot 2015-02-23 at 12.52.27 PM.png

Goal:

In each row that has Column 3 be "B753", column 7 = "no", and column 9 blank (""), I need to take the higher of column 4 or 5 (shown in bold, with the lesser in strikethrough) and sum them all.

In the example here the sought answer is 2:58 + 5:34.

I have about 100 row to look through.

I don't think this can be done in a vector, but rather an array.

Anyone?

[daddylonglegs]

Assuming your first column is column A you can use an "array formula like this:

=SUM(IF(C2:C100="B753", IF(G2:G100="No",IF(I2:I100="",IF(D2:D100>E2:E100,D2:D100,E2:E100)))))

confirmed with CTRL+SHIFT+ENTER

[Jocamo]

Thanks for the get-back.

That was fast, and elegant. Thanks. My weakness with arrays is recency of use, which leads to confusion when formatting the strings. I was stumbling going down an "if(and(" route rather than nesting just the "if's".

Thanks again.

[daddylonglegs]

Yes, you can't use AND because it returns a single result rather than an array - I think nested IFs is possibly marginally more efficient but the other way is to use * to join conditions, simulating an "AND"

[Jocamo]

I like the nested IFs. I have about 8 or 10 different conditions to which this has to apply. Just dragging up and down, replacing the key variables to make it all work.