Sum of an Array Already Within An Array Formula Returning Zero

[heyrobby]

I have an array which is produced using the following:

{=(COUNT(OFFSET($I$2:$I$6,,(E9-(A15:A17)))))}

When I select 3 cells and enter this formula it produces the desired values. However, if I try to SUM or SUMPRODUCT this formula it returns zero.

Is this possible to SUM or SUMPRODUCT or is there an alternative method that I am missing?

Any help would be greatly appreciated.

 

[XOR LX]

Hi,

I'm a bit confused here.

Your original formula is being entered as a multi-cell array formula, i.e. over a region of three cells, correct?

So how are you entering your SUM/SUMPRODUCT version? Surely not also as a multi-cell array formula over three cells, which would not make any sense (since the results would be precisely the same as those produced by your original formula).

So I take it you're entering that version within just a single cell, in which case you'll need something like:

=SUMPRODUCT(SUBTOTAL(2,OFFSET($I$2:$I$6,,E9-A15:A17)))

We could also use COUNTIF here, i.e.:

=SUMPRODUCT(COUNTIF(OFFSET($I$2:$I$6,,E9-A15:A17),{">=0","<0"}))

In general, however, we need to switch to such functions (SUBTOTAL, COUNTIF) because, unlike COUNT, they can accept an array of range references.

Regards

 

[heyrobby]

Thanks XOR LX, that was exactly what I needed

 

[XOR LX]

You're welcome!

Cheers