min and if statement again

[RichardO]

Hello all: I posted something similar, but I think I didn't explain th
problem clearly.
I have the following data, say column A, B & C

pay 260000 2
250000 3
pay 120000 4
130000 5

How do I find the min value in a column with 2 if conditions.

Someone helped with the following formula: have the followin
formula: =MIN(IF((B1:B4<250000)*(A1:A4<>"pay"),C1:C4)). The correc
answer in column C should be 2, but I am getting 5.

Basically, I want excel to ignore rows with "pay" that also have a
less than 250000. So, in this case, excel will ignore row 3 and the
the answer in column C should be 2.

Thanks very much for helping out.


RichardO

 

[Frank Kabel]

Hi
try the array formula
=MIN(IF((B1:B4>=250000)+(A1:A4<>"pay"),C1:C4))

 

[RichardO]

Hello Frank,

Thanks much. I think I am getting the correct answer. Could yo
please explain the formula to me?

If I were to count the number of items in column C with entries of 5 o
less, using the same conditions, would I use the following as an arra
formula:

=count(IF((B1:B4>=250000)+(A1:A4<>"pay"),C1:C4<=5)) but I am gettin
"0", the answer should be 3 in the sample data I provided earlier.

Thanks for your patience.


RichardO

 

[Frank Kabel]

Hi
try
=SUMPRODUCT(((B1:B4>=250000)+(A1:A4<>"pay")>0)*(C1:C4<=5))