Trouble with Countif using MAX

[jmkelly1988]

=IF(SUMPRODUCT(COUNTIF(F2:F7,"*"&'GraphData'!BP2:DR2&"*"))>0,COUNTIF(MAX(IF(H2:H7=H2,I2:I7)),">115"),0)

The above code is what I'm having problems with. I have blank cells including:

H3:H7, I6,I7

Any help greatly appreciated.

 

[Tetra201]

=IF(SUMPRODUCT(COUNTIF(F2:F7,"*"&'GraphData'!BP2:DR2&"*"))>0,COUNTIF([COLOR=#ff0000]MAX(IF(H2:H7=H2,I2:I7))[/COLOR],">115"),0)

The above code is what I'm having problems with. I have blank cells including:

H3:H7, I6,I7

Any help greatly appreciated.

1) What are trying to achieve?
2) MAX(IF(H2:H7=H2,I2:I7)) is number, not a range or array.

 

[jmkelly1988]

I'm trying to find the largest number in range I2:I7 determine where its less than 115 and if so = 1

 

[Tetra201]

I'm trying to find the largest number in range I2:I7...

That would be simply MAX(I2:I7).

... determine where its less than 115...

Could you please clarify?

... and if so = 1

And if not?

 

[jmkelly1988]

Basically want to use the countif(Max(I2:I7)"<115"

But like you said its not a range once you use the Max function. So I don't know how to right it where it will work.

 

[Tetra201]

Basically want to use the countif(Max(I2:I7)"<115"

But like you said its not a range once you use the Max function. So I don't know how to right it where it will work.

That would be

--(MAX(I2:I7)<115)

returning 1 or 0.

 

[jmkelly1988]

This is great, but what if I want it to read between two numbers say greater than 115 and less than 185.

 

[Tetra201]

This is great, but what if I want it to read between two numbers say greater than 115 and less than 185.

That would be

--(AND(MAX(I2:I7)>115,MAX(I2:I7)<185))

returning 1 or 0.

 

[jmkelly1988]

I'm not sure if that was easy to do but thanks a million!!!! Wish I could up vote you or something!!!

 

[Tetra201]

You are welcome.