Search code examples
excelexcel-formulasumifs

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

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

Table

Solution 
  • =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)))))))

    enter image description here

    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))))))

    enter image description here

    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.