Array formulas - using Subtotal function?

[JROM]

Is it possible to use the subtotal function within an array formula? I've used array formulas for sum, count, average; and they work fine, but when I tried using subtotal, I get an error message.

Does anyone know if this works or not?

 

[Domenic]

Try...

=SUMPRODUCT(--(CondRange="Cond"),SUBTOTAL(9,OFFSET(RangeToSum,ROW(RangeToSum)-MIN(ROW(RangeToSum)),0,1)))

Note that if the condition is a numerical value, remove the quotes.

Hope this helps!

 

[JROM]

Thank you so much for your help. That worked beautifully, and gives the exact results that I'm looking for!

Only one question, though. How does that formula actually work? I've tried to step through the formula, and have tried to look up each of the functions in the help, but still can't really understand why it works.

Would it be possible to explain it in layman terms to me?

 

[Domenic]

Thank you so much for your help.

You're very welcome!

How does that formula actually work?

Have a look here...

 

[JROM]

Thanks again! That link explained how the "offset" portion of the formula worked. It hurt my brain, but I'm pretty sure I understood it.

Now, for the first part of the formula:

=SUMPRODUCT(--(CondRange="Cond"),

What does the -- mean? It almost looks like either an IF formula, or a WHERE formula, but I've never seen that used before.

 

[Domenic]

The conditional statement...

CondRange="Cond"

...returns an array of TRUE and FALSE. The double negative '--' coerces these values into their numerical equivalent, 1 and 0 respectively. Have a look here..

 

[JROM]

Ahhhhhh - I get it now!

Thanks again for all your help!