Hi,

I don't know how much this will improve the efficiency but if the range
unchanging you can define a name, say T which is equal to
=IF($A$1:$A$5$A$7:$A$11,$A$1:$A$5,$A$7:$A$11)-$A$13:$A$17
then you spreadsheet formula becomes
=SUMPRODUCT(--(T0),T)
which does not need to be arry entered. If you want the ranges to be
relative then naming the range is a little tricker, select the cell where you
are going to enter the formula and then choose Insert, Name, Define, enter T
as the name and in the Refers to box enter
=IF(A1:A5A7:A11,A1:A5,A7:A11)-A13:A17
The formula in the spreadsheet is the same as above.

I also notice that even if you don't consider any of these ideas you can
simplify the original suggestion to

=SUMPRODUCT(--(IF(A1:A5A7:A11,A1:A5,A7:A11)-A13:A170),IF(A1:A5A7:A11,A1:A5,A7:A11)-A13:A17)

All that's changed is the -- in the second part of the formula. It still
needs to be entered as an array.

Cheers,
Shane Devenshire

"Shane Devenshire" wrote:

Hi,

Didn't test, try this instead:

=SUMPRODUCT(--(IF(A1:A5A7:A11,A1:A5,A7:A11)-A13:A170),--IF(A1:A5A7:A11,A1:A5,A7:A11)-A13:A17)

Array entered. I don't know if this is the result you want but with dummy
data I get a result.

Cheers,
Shane Devenshire

"Anthony" wrote:

On Nov 25, 11:06 am, Shane Devenshire
wrote:
Hi,

You might consider an SUMPRODUCT function such as:

=SUMPRODUCT(--(IF(A1:A5A7:A11,A1:A5,A7:A11)-A13:A170),--(A1:A5A7:A11,A1:Â*A5,A7:A11)-A13:A17))

If this helps, please click the Yes button

Cheers,
Shane Devenshire


Hi Shane

I tried your suggestion, but it just gives a #VALUE error (even if i
press Ctrl Shift Enter to make it an array formula)

Anthony