Is there any way to sumif with an if condition in the criteria?-CodePudding

I am trying to create a SUMIF() formula in excel that has a if condition built in

It should run through the A column and look for kosher then go to B column and check for Albany, but it should also count the "non-albany."

However, if I check for San Fran, it should run through both but the result should exclude row 4 bc it belongs to the Albany group

Expected Results:
Albany = 3
San Fran = 1
Ok = 1

CodePudding user response：

=LET(data,A1:B5,
     a,INDEX(data,,1),
     b,INDEX(data,,2),
     c,IF(a="non-albany","albany",b),
     u,UNIQUE(b),
CHOOSE({1,2},
       u, 
       BYROW(u,LAMBDA(x,
             LET(y,FILTER(c,c=x,0),
             SUM(IF(y=0,0,1)))))))

Or if you want to sum the values of column C conditionally, you could use:

=LET(data,A1:C5,
     a,INDEX(data,,1),
     b,INDEX(data,,2),
     c,INDEX(data,,3),
     d,IF(a="non-albany","albany",b),
     u,UNIQUE(b),
CHOOSE({1,2},
       u, 
       BYROW(u,LAMBDA(x,             
             LET(y,FILTER(c,d=x,0),
             SUM(y))))))

This could also be accomplished with MMULT for older versions:

=MMULT(N(D12:D15=TRANSPOSE(IF(A1:A5="non-albany","albany",B1:B5)))*TRANSPOSE(C1:C5),ROW(C1:C5)^0)

Where D12:D15 hold the unique values from column B.

CodePudding user response：

Requirement :

1] It should run through the A column and look for kosher then go to B column and check for Albany, but it should also count the "non-albany"

2] However, if I check for San Fran, it should run through both but the result should exclude row 4 bc it belongs to the Albany group

Formula in F1, copied down :

=SUMPRODUCT(ISNUMBER(SEARCH("kosher",$A$1:$A$5))*($B$1:$B$5=E1),$C$1:$C$5) SUMPRODUCT((LOWER($A$1:$A$5)="non-albany")*ISNUMBER(SEARCH(E1,$A$1:$A$5)))