Value of my formula goes to 0 when applying ArrayFormula with added condition.

Initially I used:
=ArrayFormula(max(if(Simplified!$B$4:$B$267=$K$2,Simplified!$E$4:$E$267)))
to find the maximum value with one condition.

It works but when I added another condition which is
=ArrayFormula(max(if(AND(Simplified!$B$4:$B$267=$K$2,Simplified!$K$4:$K$267=$K$3),Simplified!$E$4:$E$267)))
it returns a value of 0

Here's the link of the gsheets: https://docs.google.com/spreadsheets/d/1AnbX80qgZjAArw4JUMo3rpIBtkm-BcACwb0FWpJzvE8/edit?usp=sharing

Help would be deeply appreciated!

closed as off-topic by Máté Juhász, n8te, Pimp Juice IT, music2myear, kinokijuf Apr 12 at 1:01

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "This question is not about computer hardware or software, within the scope defined in the help center." – n8te, Pimp Juice IT, music2myear
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 2
    Questions about google spreadsheets (˙ArrayFormula()˙ doesn't exist in Excel) are off-topic here, should be asked at webapps stackexchange. – Máté Juhász Apr 9 at 8:25
  • @MátéJuhász is there a way to (quickly) determine if a function/feature is present/absent? I sometimes use one to answer questions about the other (G Sheets / Excel / Calc) – bertieb Apr 9 at 15:21
  • Check the attached goggle Sheet I've Written the same Formula as shown below in my Answer and its working, given same result 446.263. – Rajesh S Apr 9 at 16:12

Write your formula like this:

=ArrayFormula(MAX(IF(Simplified!$B$4:$B$267=$K$2,IF(Simplified!$K$4:$K$267=$K$3,Simplified!$E$4:$E$267))))

Or, you can write the formula as shown below and enter it with Ctrl + Shift + Enter. Google Sheets will automatically add the reserved word 'ArrayFormula' to the formula:

=MAX(IF(Simplified!$B$4:$B$267=$K$2,IF(Simplified!$K$4:$K$267=$K$3,Simplified!$E$4:$E$267)))

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