Max(If Formula

[btadams]

I have data like this:

Column A = dates
Column C = values


and I'm trying to create a formula that will return the maximum value in column C if the month in column A is equal to the month of a given date. Here's what I've been trying to use but it gives me a value of zero:

=MAX(IF(MONTH($F$3)=MONTH($A$11:$A$100),$C$11:$C$100))

as an array formula (Ctrl+Shift+Enter). Does anyone have any suggestions?

Thanks,

 

[lenze]

Try it this way

=MAX(IF(MONTH($A$11:$A$100)=MONTH($F$3),$C$11:$C$100))

lenze

 

[T. Valko]

I have data like this:

Column A = dates
Column C = values


and I'm trying to create a formula that will return the maximum value in column C if the month in column A is equal to the month of a given date. Here's what I've been trying to use but it gives me a value of zero:

=MAX(IF(MONTH($F$3)=MONTH($A$11:$A$100),$C$11:$C$100))

as an array formula (Ctrl+Shift+Enter). Does anyone have any suggestions?

Thanks,

Your formula looks OK so as long as you did in fact array enter it then that means either there are no dates that fall within the same month or the range C11:C100 doesn't contain any numbers that correspond to that date.

The numbers might be TEXT numbers which are different than NUMERIC numbers.

Find a cell where the dates meet the criteria and the number in column C looks like the max value. Test the cell in column C to see if it is in fact a numeric number.

Let's assume cell C10 is that cell.

=ISNUMBER(C10)

What result do you get from that formula?

 

[btadams]

T. Valko,

You are correct. I've once again fallen for the old numbers-as-text scam. I'm such a ****** for that. (Fool me once, shame on you; fool me every time, Doh!)

Lenze,

Thanks for your time and response!

 

[texasalynn]

or another option

=MAX(IF(MATCH(MONTH($F$3),MONTH($A$11:$A$100),0),$C$11:$C$100))

array formula so use Ctrl+Shift+Enter

 

[T. Valko]

T. Valko,

You are correct. I've once again fallen for the old numbers-as-text scam. I'm such a ****** for that. (Fool me once, shame on you; fool me every time, Doh!)

Lenze,

Thanks for your time and response!

Ok, glad to have shed some light.

Just hope the fix was painless!