1

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?

0

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

  • 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? – Ceebee86 Jun 28 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.stack.imgur.com/r6u79.png – user0 Jun 28 at 13:48
  • 1
    Whooooa, works amazingly well!!! Thank you! :)) – Ceebee86 Jun 28 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. – Ceebee86 Jul 3 at 10:45
  • Don't worry, worked it out. :) – Ceebee86 Jul 3 at 11:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.