• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

MAX based on 3 or more conditions (using numerics alphabets)

T

tinaaaks

New Member
I'm trying to find the MAX number using 3 conditions, which I'll be referencing from another sheet.


*Remember, condition 1 and 2 must be true ALWAYS before condition 3 can take place.


Condition 1: Find 'EEE' in column A.

Condition 2: In column B ('DATE' in format MM/DD/YEAR), find January, "<=01/31/2013" and ">=01/01/2013".

Condition 3: In colum C, find the MAX number (after testing conditions 1 & 2 are true).


Here is what I am using and I don't know what I'm doing wrong:

=MAX(IF(Sheet3!$A$2:$A$500)=EEE, Sheet3!B2:B500=<=01/31/2013, Sheet3!B2:B500, =>=01/01/2013, Sheet3!C2:C500))
 
Try this as an array


=MAX((Sheet3!$A$2:$A$500="EEE")*(MONTH(Sheet3!$B$2:$B$500)&YEAR(Sheet3!$B$2:$B$500)="12013")*(Sheet3!$C$2:$C$500))
 
Thank you SAM, though, the output that I received was 0.


I've copied and pasted a few data below. I think this might help to understand the process.


NAME DATE HOURS

EEE 1/14/2013 0.75

CCC 2/21/2013 2

EEE 2/28/2013 2

EEE 1/28/2013 3

CCC 3/7/2013 0.75

ABC 2/13/2013 6.5

EEE 1/13/2013 6.5


So what I want is the code to first check if it's EEE, then check if it falls between January 1-31, and then holding the 2 conditions true, check to see MAX. In this case, the MAX should output 6.5.
 
that's because you haven't entered the formula as an array formula. To confirm an array formula, you need to enter it with CTRL+SHIFT+ENTER
 
I did do a CTRL+SHIFT+ENTER and even the brackets show up. Though, the value I get is 0. How can we debug that?
 
Hi Tina ,


Hopefully by now you would have found out that Sam's formula works. If not , please check your sheet reference Sheet3 has the data you mention.


To know where your formula was wrong , correct it as follows :


=MAX(IF(Sheet3!$A$2:$A$500="EEE", IF(Sheet3!B2:B500<=DATEVALUE("01/31/2013"), IF(Sheet3!B2:B500>=DATEVALUE("01/01/2013"), Sheet3!C2:C500))))


to be entered as an array formula , using CTRL SHIFT ENTER.


You can see the differences between this version , which works , and your initial one.


Narayan
 
Hi


Give the following non array formula a try. Put your 1st Criteria (EEE) in Cell E1, your second criteria Date 1 in F1 and Date 2 in G1. Combine that with this formula.


=SUMPRODUCT(MAX(($A$2:$A$500=E1)*($B$2:$B$500>F1)*($B$2:$B$500<G1)*($C$2:$C$500)))


Take care


Smallman
 
Also adding a reference to Sheet3.


=SUMPRODUCT(MAX((Sheet3!$A$2:$A$500=E1)*(Sheet3!$B$2:$B$500>F1)*(Sheet3!$B$2:$B$500<G1)*(Sheet3!$C$2:$C$500)))


Take care


Smallman
 
Thank you very much to all for helping out, especially to SAM.


I'd also like to apologize to SAM. Your formula worked because I just tried your codes and it works. But for some reason, when I do the same thing in my final spreadsheet, then it doesn't work. The file is saved as '.xlxs', but I doubt this is the reason why the same codes don't work on the final spreadsheet. I haven't created the spreadsheet with data. I'm just adding some minor codes as a touch-up.


Let me know if anyone has any idea why the data is not working on my final spreadsheet in .xlxs format.


Thank you a lot,

Tina
 
A shorter approach, and one that's more robust against accidental text entries, is:

=MAX((TEXT(B2:B11,"mmyyyy")="012013")*(A2:A11="EEE")*(C2:C11))

Confirmed as an array formula.

Using the TEXT function lets you have a bit more flexibility when dealing with dates.
 
You must log in or register to reply here.
Back
Top