Google Sheets – Using ARRAYFORMULA with IF and SUMIFS Functions

formulasgoogle sheetsgoogle-sheets-arrayformulagoogle-sheets-queryvlookup

I am trying to convert the below formula into an array formula. I can make it work in an array, but it displays the wrong values, compared to the normal formula which I have to drag down.

Normal formula:

=if(K4>0,
 (if(((sumifs($K$4:$K,$A$4:$A,A4)-(sumifs($L$4:$L,$A$4:$A,A4))))=0,11,
 (if((sumifs($M$4:$M,$A$4:A,A4))>0,5,)))),12)

I tried the below in an array formula, and it skips value 5 and only displays 11 or 12.

Array formula:

=arrayformula({"";if(K4:K>0,(if(((sumifs(K4:K,A4:A,A4:A)-
 (sumifs(L4:L,A4:A,A4:A))))=0,11,(if((sumifs(M4:M,A4:A,A4:A))>0,5,)))),12)})

I then use a vlookup to look against the value, that the if function provides against a list of status messages.

What am I doing wrong?

Best Answer

={""; ARRAYFORMULA(IF(K4:K>0, 
 IF(VLOOKUP(A4:A, QUERY({A4:A, K4:K}, 
 "select Col1,sum(Col2) where Col1 is not null group by Col1", 0), 2, 0)-
    VLOOKUP(A4:A, QUERY({A4:A, L4:L}, 
 "select Col1,sum(Col2) where Col1 is not null group by Col1", 0), 2, 0)=0, 11, 
 IF(VLOOKUP(A4:A, QUERY({A4:A, M4:M}, 
 "select Col1,sum(Col2) where Col1 is not null group by Col1", 0), 2, 0)>0, 5)), 12))}

0

Related Topic