On Nov 25, 11:51*am, 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,A*1: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- Hide quoted text -

- Show quoted text -

Shane

That works, but i think i have found an even more efficient method:

=MAX( MAX( sum(A1:A5), sum(A7:A11) ) - sum(A13:A17), 0)

It seems to work OK from my initial tests.

Anthony