CODEWITHSUNDEEP
excelexcel-formulaoffice365
dfunky1
dfunky1

Reputation: 5

Excel - SUM or SUMIF all columns of a 2D array where rows are filtered by a horizontal function

I want to SUM multiple columns but only for rows whose SUM is >0. One approach that works is to add an extra column that in essence excludes rows whose SUM is <0 and then sum that extra column as in the screenshot below. My desire is to eliminate the helper column and create a single formula based only on the original data that returns the same result. I've tried to include FILTER() inside a SUMIF() and a few other possibilities but to no avail.

Note: I am on O365 with dynamic array support if that helps. Thanks in advance.

Desired output in cell G2, but without the need of formula in column E

Upvotes: 0

Views: 1320

Answers (2)

bosco_yip
bosco_yip

Reputation: 3802

In G2, enter formula :

=SUMPRODUCT((MMULT(A2:C4,{1;1;1})>0)*MMULT(A2:C4,{1;1;1}))

enter image description here

Upvotes: 1

Dang D. Khanh
Dang D. Khanh

Reputation: 1471

If I understand what you mean, using the MMult function will help convert the matrix A2:C4 to E2:E4 as you say.

-You can use the LET function, if you have Office365-Insider,

=LET(x,A2:C4,y,MMULT(x*1,SEQUENCE(COLUMNS(x))^0),SUM(y*(y>0)))

-Or missing LET:

=SUM(MMULT(A2:C4*1,SEQUENCE(COLUMNS(A2:C4))^0)*(MMULT(A2:C4*1,SEQUENCE(COLUMNS(A2:C4))^0)>0))

The reason I use x*1 is to avoid possible errors if you leave a cell blank.

Upvotes: 2

Related Questions