SumIF ByColumn, looking through an INDEX array

Question

Hi,

I'm trying to SUMIF the values in a column are greater than 0. I've created a range/array (sorry - not sure which I've created) by using INDEX to get a 2D grid.

A1:D3 was created using an index =INDEX($A:$AZ,ROW(G3:G5),COLUMN(G3#))

The BLUE Spilled Range was created using =BYCOL(A1#,LAMBDA(c,SUMIF(c,">0")))

Question: Instead of referencing A1#, how can I get the INDEX result into the ByCol formula? I'm currently getting a #CALC error because of an array of arrays.

I was partially successful with the GREEN Spilled Range by using SumProduct =BYCOL(INDEX($A:$AZ,ROW(G3:G5),COLUMN(G3#)),LAMBDA(c,SUMPRODUCT(c*(c>0))))

But I can't get SumProduct to ignore texts.

Thank you all.

Answer 1

Use the INDEX expression directly inside BYCOL and coerce the text values to zero inside the LAMBDA.

For example, with the 2‑D grid returned by

=INDEX($A:$AZ,ROW(G3:G5),COLUMN(G3#))

a BYCOL that sums only values > 0 and ignores text can be written as:

=BYCOL(
    INDEX($A:$AZ,ROW(G3:G5),COLUMN(G3#)),
    LAMBDA(c,
        SUM(IF(c>0,c))
    )
)

Explanation:

• INDEX($A:$AZ,ROW(G3:G5),COLUMN(G3#)) returns a single 2‑D array, so BYCOL receives one array (no array‑of‑arrays, so no #CALC!).
• Inside the LAMBDA, IF(c>0,c) builds an array that contains each positive number and FALSE for everything else (negative numbers, zeros, and text).
• SUM ignores the non‑numeric FALSE values, so text is effectively ignored while only positive numbers are summed.

If needed, the same pattern can be adapted to other aggregate functions, for example:

=BYCOL(INDEX($A:$AZ,ROW(G3:G5),COLUMN(G3#)),LAMBDA(c,AVERAGE(IF(c>0,c))))

(entered as a dynamic array formula).

---

References:

• Guidelines and examples of array formulas
• SUMIF function