# Array Formula with IF function and SUMIFS function

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?

``````={""; 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))}``````

• Hi User0, thats pretty damn sweet! Thank you :) but...For some reason, if I add a letter after 55555 for example, the first line in O will show N/A, but 2nd line will show 12. Is there a way to get round that? Commented Jun 28, 2019 at 13:42
• try: `={""; ARRAYFORMULA(IF(K4:K>0, IF(VLOOKUP(TO_TEXT(A4:A), QUERY({TO_TEXT(A4:A), K4:K}, "select Col1,sum(Col2) where Col1 is not null group by Col1", 0), 2, 0)- VLOOKUP(TO_TEXT(A4:A), QUERY({TO_TEXT(A4:A), L4:L}, "select Col1,sum(Col2) where Col1 is not null group by Col1", 0), 2, 0)=0, 11, IF(VLOOKUP(TO_TEXT(A4:A), QUERY({TO_TEXT(A4:A), M4:M}, "select Col1,sum(Col2) where Col1 is not null group by Col1", 0), 2, 0)>0, 5)), 12))}` - i.sstatic.net/r6u79.png Commented Jun 28, 2019 at 13:48
• Whooooa, works amazingly well!!! Thank you! :)) Commented Jun 28, 2019 at 13:51
• Hi User0, I've tried amending this to include another question. Wondered if you could help? If L is >0 show 6. I amended the spreadsheet to help you get an idea what I mean. Commented Jul 3, 2019 at 10:45
• Don't worry, worked it out. :) Commented Jul 3, 2019 at 11:36