min and if statement

  • Thread starter Thread starter RichardO
  • Start date Start date
R

RichardO

Hello all:

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

I have the following formula: Min(data!$R:$R)

I have items in column W such as pay, auto e.t.c. and dollar amounts i
column L. I want excel to find the min in column R for everythin
EXCEPT for cases where column W and column L have "pay" AND amount
dollar amount less than "250" respectively.

Thanks for your help.


RichardO
 
=MIN(IF((L1:L1000>=250)*(W1:W1000<>"pay"),R1:R1000))

Array-entered, meaning hold down the Shift key and the
Ctrl key, and press Enter.

HTH
Jason
Atlanta, GA
 
How do I find the min value in a column with 2 if conditions.

I have the following formula: Min(data!$R:$R)

I have items in column W such as pay, auto e.t.c. and dollar amounts in
column L. I want excel to find the min in column R for everything
EXCEPT for cases where column W and column L have "pay" AND amount $
dollar amount less than "250" respectively.
Click to expand...

Once you add the expressions needed to exclude W="pay" and L<250 you can't use
entire column ranges. If row 1 contains text, such as field names, then you
could try the array formula

=MIN(IF((data!$W$2:$W$65536<>"pay")+(data!$L$2:$L$65536>=250),
data!$R$2:$R$65536))
 
Hi Jason,
Thanks, so much, it worked. Instead of finding the min, if I want t
count the number of entries would the following formula be correct?

Count(IF((L1:L1000>=250)*(W1:W1000<>"pay"),R1:R1000))
but it's not working.

Thanks once again.


RichardO
 
Sorry I am reposting my last reply because I omitted something:

Instead of finding the min, if I want to count the number of entries i
R less than "15" would the following formula be correct?

Count(IF((L1:L1000>=250)*(W1:W1000<>"pay"),R1:R1000<15))
but it's not working.

Thanks once again.


RichardO
 
=SUMPRODUCT(--(L1:L1000>=250),--(W1:W1000<>"pay"),--(R1:R1000)<15))

The COUNT(IF(...)) formulas for multiconditional conditional are slower than
corresponding SumProduct formulas.

You could substitute for --(W1:W1000<>"pay") a conditional that directly
addresses the non-pay set if that set is known:

--ISNUMBER(MATCH(W1:W1000,{"x","y","z"},0))

where {"x","y","z"} is the non-pay set (or complement of {"pay"}).
 
Back
Top