Advanced If Function

  • Thread starter Thread starter Luciano
  • Start date Start date
L

Luciano

Hi,
I have a table.In one column(A) I have the names of the people in mix
order.In column(B) I have the telephone area codes.I want to select the
records where the first name starts with A,B or C and where the
telephone area code is between 150-350.
How can I write my function?
I tired seveal times and everytime I got invalid.
Thank you.
 
Try this ... May not be simpliest way, but I think it
works ... In Col "C" (or Col of your choice) ...

=IF(AND(OR(LEFT(A1,1)="A",LEFT(A1,1)="B",LEFT(A1,1)
="C"),AND(B1>=150,B1<=350)),A1,"")

HTH ... K
 
If select means count then
=sumproduct((a2:a200={"A","B","C"})*(b2:b200>=150)*(b2:b200<350))
if select means to show those, use data>filter>autofilter
 
Thank you Don Guillett

I think now I am closer what I want.But I have a question;

I select all Column(A) where the names are.The
Data->Filter->AutoFilter. Then I chose "custom" in drop down menu.Ther
in drop down menu on the left top,I chose "begins with".But there I ca
only write one letter.But I want the names which start with A,B or C
 
Don;
I had to insert the Left(a2:a200,1) piece in the Sumproduct() approach in
order to make it work;
Can you confirm?
Tks,


Don Guillett said:
If select means count then
=sumproduct((a2:a200={"A","B","C"})*(b2:b200>=150)*(b2:b200<350))
if select means to show those, use data>filter>autofilter
Click to expand...
 
I missed the "starts"
first name starts with A,B or C
So, your mod should work.
=sumproduct((left(a2:a200,1)={"A","B","C"})*(b2:b200>=150)*(b2:b200<350))

--
Don Guillett
SalesAid Software
(e-mail address removed)
JMay said:
Don;
I had to insert the Left(a2:a200,1) piece in the Sumproduct() approach in
order to make it work;
Can you confirm?
Tks,
Click to expand...
 
Tks Don
JMay

Don Guillett said:
I missed the "starts"
first name starts with A,B or C
So, your mod should work.
=sumproduct((left(a2:a200,1)={"A","B","C"})*(b2:b200>=150)*(b2:b200<350))
Click to expand...
 
Back
Top