I have noticed that SUMIF does not play well with matrix formulas.
To save complicated explanations of my motivation, here is a sample that demonstrates the problem:
= SUMIF (ROW (INDIRECT ("1:10")), "<5")
My current solution is to repeat the criteria in a logical statement to filter the relevant values in a SUM function:*
= SUM (ROW (INDIRECT ("1:10")) * (ROW (INDIRECT ("1:10")) <5)
ButMy real formula is quite long and I want to avoid repeating it for simplicity and ease of maintenance.
Can someone help me understand what is happening?