Count the # of times the data appears based on multiple conditions [SOLVED]

[tinaaaks]

I'm trying to COUNT the number of times the data appears using 3 conditions, which I'll be referencing from another sheet.


Condition 1: Find 'EEE' in column A.

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

Condition 3: In colum C, count number of times the data appears (after testing conditions 1 & 2 are true).


Here is the formula that I had used with your help for counting the MAX.

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


Thank you very much for helping out.

 

[tinaaaks]

Correction:

I meant find JULY, not January for Condition 2.


And the CORRECT FORMULA for MAX is:

=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))

 

[Sara]

Try this;

=COUNTIFS(Sheet3!$A$2:$A$500,"EEE",Sheet3!$B$2:$B$500,">=07/01/2013",Sheet3!$B$2:$B$500,"<=07/31/2013")


If you need to be able to alter the dates, it would pay to put them in a cell, say A1 and A2. If you do, you'll need to alter the last part

=COUNTIFS(Sheet3!$A$2:$A$500,"EEE",Sheet3!$B$2:$B$500,">="&A1,Sheet3!$B$2:$B$500,"<="&A2)

 

[Smallman]

Hi


If tinaaaks does not have the latest version of XL then the following might help.


=SUMPRODUCT(MAX((MONTH(B$2:$B$500)=7)*(A$2:$A$500="EEE")*($C$2:$C$500)))


An improvement would be to NOT have hard coded text within the formula. This would give you a bit more flexibility.


Like Sara's solution the above is Not an array formula so does not need Ctrl Shift Enter like your current formula.


Take care


Smallman

 

[tinaaaks]

I just want to say THANK YOU to Sara and Smallman.