SUM doesn't work in a long array formula

Hi!

I'm trying to use a SUM that is working on its own in a large formula that I need to operate as an array (CSE) but inside that long formula it just calculates 0.

This is the sum I want to use: SUM(VALUE($Q$2:INDIRECT("Q"&ROW()-1))) - Basically I want to sum up all the values on top of a given one in a column.

When I include it in the bigger formula and I evaluate it, I get SUM(#VALUE) so result is 0, but when I use it in a normal cell, even if I execute it as CSE, it works.

As for the part in the bigger formula that needs to be operated in an array is this one:

SUM(IF(CONDITION,1/RANGE)) - I'm trying to sum the reciprocals of certain values if they meet a condition (1/n+1/m+1/p...etc) This is the only way I've managed to do it

Any ideas on why this is happening?

Thanks!