Max( based on criteria )

[Somendra Misra]

Hi Max,
See the attached file. Yellow Cells has formula. Kindly check on your original file.

Regards,

 

[Max Alejandro Molina]

This is great! thank you so much!

 

[Max Alejandro Molina]

Somendra, what if I would also see the ordered quantity related to the date shown in column M:M??

 

[Haseeb A]

Max Molina,

You have used bunch of volatile formulas in the sheets. Volatile formulas will cause a workbook performance. So better to avoid, if alternate are available. Try the below formulas,

M3, Array Formula with CTRL+SHIFT+ENTER

=IFERROR(LARGE(IF(DataBase!D$3:D$113=K3,IF(INDEX(DataBase!F$3:H$113,,MATCH(C3,DataBase!F$2:H$2,0))<>0,DataBase!I$3:M$113)),1),"")

N3, Array Formula with CTRL+SHIFT+ENTER

=IFERROR(TRIM(RIGHT(SUBSTITUTE(INDEX(DataBase!A$2:M$2,SMALL(IF(DataBase!D$3:D$113=K3,IF(INDEX(DataBase!F$3:H$113,,MATCH(C3,DataBase!F$2:H$2,0))<>0,IF(DataBase!I$3:M$113=M3,COLUMN(DataBase!I$2:M$2)))),1)),".",REPT(" ",99),1),99)),"")

O3, with just ENTER

=IF(M3="","",M3+LOOKUP(9999999999,RIGHT(SUBSTITUTE(N3,".",""),{1,2,3,4,5,6,7,8,9})+0))

Q3, with just ENTER

=IFERROR(SUMIFS(INDEX(DataBase!F:H,0,MATCH(C3,DataBase!F$2:H$2,0)),DataBase!D:D,K3,INDEX(DataBase!I:M,0,MATCH("*"&N3,DataBase!I$2:M$2,0)),M3),"")

Then copy down all formulas.

Also, for column P, enter today's date in a cell & link that cell to the formula.

ie, enter in A1: =TODAY(), then in P3: =A$1+(ROUND(J3,0)-O3)

 

[Max Alejandro Molina]

Thank you very much!!!

 

[Max Alejandro Molina]

Hello Haseeb!,

I have a restriction but i dont know how to solve it with a formula, as you can see in the attached file I have two dates equal (in the cells filled with red color) and excel is taking the light red filled cell intead of the last one, can we modify the formula so that it chooses the last value from left to right and when its duplicated like this scenario that it takes the one that it is to the very right side?

 

[Haseeb A]

Try this Array Formula in N3,

=IFERROR(TRIM(RIGHT(SUBSTITUTE(LOOKUP(2,1/(INDEX(DataBase!I:M,LARGE(IF(DataBase!D$3:D$113=K3,IF(INDEX(DataBase!F$3:H$113,,MATCH(C3,DataBase!F$2:H$2,0))<>0,IF(DataBase!I$3:M$113=M3,ROW(DataBase!D$3:D$113)))),1),0)=M3),DataBase!I$2:M$2),".",REPT(" ",99),1),99)),"")

Then copy down.

 

[Max Alejandro Molina]

Thanks Haseeb, In formula:

=IF(M74="","",M74+LOOKUP(9999999999,RIGHT(SUBSTITUTE(N74,".",""),{1,2,3,4,5,6,7,8,9})+0))

I want also that if it finds a the text "Entrega" in cell N3 and also if cell J3<45 then i want the cell O3 to show "Colocar PO", and if this is not true then do what the initial formula does.

 

[Haseeb A]

O3 & copy down.

=IF(M3="","",IF(ISNUMBER(SEARCH("Entrega",N3)/(J3>0)/(J3<45)),"Colocar PO",M3+LOOKUP(9999999999,RIGHT(SUBSTITUTE(N3,".",""),{1,2,3,4,5,6,7,8,9})+0)))

 

[Max Alejandro Molina]

Thanks Haseeb, I have a last question. I dont know why the formula is giving me something diferent, as you can se on the EjercicioL&Q workbook the column O148 should be giving me the same thing as the two other columns to its left, but i dont know why thi isnt hapenning, can you look through this please?

 

[Haseeb A]

Max Molina,

Check the formula in these 3 cells. You can see something different than other cells; links to other files.

Copy cell O3 & paste down. Will be OK.

 

[Max Alejandro Molina]

Max Molina,

Check the formula in these 3 cells. You can see something different than other cells; links to other files.

Copy cell O3 & paste down. Will be OK.

Would you please update the correct file, because i cannot find the difference

 

[Haseeb A]

See attached. O3 formula should be

=IFERROR(LARGE(IF(DataBase!$D$3:$D$150=K3,IF(INDEX(DataBase!$F$3:$J$150,,MATCH(C3, DataBase!F$2:H$2,0))<>0, DataBase!I$3:M$113)),1),"")

Red highlighted should D. in your file it is C

 

[Max Alejandro Molina]

Haseeb, it is C because it is the column of the other worksheet i attached previously, the problem is not within the same worksheet. I am trying to link the formula to the other worksheet but it does not work, can you please upload the formula on the red cell linked to the other worksheet attached?