IF,And,OR Help

[lostinformulas]

I need help adding another condition to my formula.I need AE9 to
"Epress" or ="EXPU". At this time it only returns a response fo
="Express"

=IF(AND(J9="yes",AK9="yes",AE9="Express"),1,"")


I also need this formula to total the column. AE9:AE200

 

[bj]

try

=IF(AND(J9="yes",AK9="yes",or(AE9="Express",AE9="Epress",AE9="EXPU")),sum(AE9:AE200),"")
Are you sure on summiing the AE9 since you are looking for text in AE9?

 

[Toppers]

=IF(AND(J9="yes",AK9="yes",OR(AE9="Express",AE9="EXPU")),SUM(AE9:AE200),"")

Note your SUM will "include" the word EXPRESS or EXPU ... will still work.

HTH

 

[Bearacade]

=IF(AND(J9="yes",AK9="yes",(OR(AE9="express", AE9="epress",
AE9="EXPU"))),SUM(AE9:AE200),"")

 

[lostinformulas]

I guess I don't need a sum but need to count the times that either
Express or EXPU appears between AE9:AE200

 

[Toppers]

=countif(AE9:AE200,"=Express")+countif(AE9:AE200,"=EXPU")

 

[Bearacade]

The following will count the number of cells that has "express" "epress"
and "EXPU" if AE9 = express, epress or EXPU:

=IF(AND(J9="yes",AK9="yes",(OR(AE9="express", AE9="epress",
AE9="EXPU"))),Countif(AE9:AE200, "express")+Countif(AE9:AE200,
"epress")+Countif(AE9:AE200, "EXPU"),"")


The following will count the number of cells that has whatever is in
AE9,
so if AE9 has express, it will count how many times express shows up,
if AE9 has epress, it will count how many times epress shows up.. and
so forth:

=IF(AND(J9="yes",AK9="yes",(OR(AE9="express", AE9="epress",
AE9="EXPU"))),Countif(AE9:AE200, AE9),"")

 

[lostinformulas]

Were getting closer.

=IF(AND(J9="yes",AK9="yes",(OR(AE9="express", AE9="epress",
AE9="EXPU"))),COUNTIF(AE9:AE200, "express")+COUNTIF(AE9:AE200,
"epress")+COUNTIF(AE9:AE200, "EXPU"),"")

This counts all the Express and EXPU between AE9:AE200. I need it to
only count these if the cooresponding J and AK = yes

J9=yes AK9=yes AE9=express
J10=no AK10=yes AE10=EXPU
J11=yes AK10=yes AE11= blank cell
j12 =yes AK12=yes AE12=EXPU

Row 9 would = 1
Row 10 would = 0
Row 11 would = 0
Row 12 would = 1

Total count = 2

Thank you everyone for your help

 

[Bearacade]

=IF(AND(J9="yes",AK9="yes",(OR(AE9="express", AE9="epress",
AE9="EXPU"))),1,0)

And whereever you have total count, just do a =SUM()

 

[lostinformulas]

OK I have a formula that is working, now I need to add another variable
to it.

The formula that I'm using is:
=IF(AND(A17="yes",AO17="yes",AQ17="HB"),1,"")

This returns a value of 1 if
all of the following is true
A17 = Yes
AO17 =Yes
AQ17 = HB

So if all of theses conditions are met I need to add if D17 = yes then
divide by 2 if not the answer would be 1 if all of the previous
conditions are met.

 

[vandenberg p]

Hello:

If I understand your question this should do it:

=IF(AND(A17="yes",AO17="Yes",AQ17="hb"),IF(D17="yes",0.5,1),"")

If not exactly perhaps you can make it work by realizing that you can nest the
if statements and so in place of the ,1, in your formula just put in the
additional test you need. So now the formula should return .5 (half of 1) if D17
is yes and all of the other conditions are true or 1 if D17 is not not yes,
if the other conditions are not true the value of D17 is not considered and
"" is returned.

Pieter Vandenberg


: OK I have a formula that is working, now I need to add another variable
: to it.

: The formula that I'm using is:
: =IF(AND(A17="yes",AO17="yes",AQ17="HB"),1,"")

: This returns a value of 1 if
: all of the following is true
: A17 = Yes
: AO17 =Yes
: AQ17 = HB

: So if all of theses conditions are met I need to add if D17 = yes then
: divide by 2 if not the answer would be 1 if all of the previous
: conditions are met.


: --
: lostinformulas
: ------------------------------------------------------------------------
: lostinformulas's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=35229
: View this thread: http://www.excelforum.com/showthread.php?threadid=550065

 

[lostinformulas]

ThankYou Vandenberg!!!!!

your formula
=IF(AND(A17="yes",AO17="Yes",AQ17="hb"),IF(D17="yes",0.5,1),"") work
perfect!!!!

One of my co-workers also came up with a formula that works, it
little longer than yours.

=IF(AND(A17="yes",AO17="yes",AQ17="HB",D17="yes"),0.5,IF(AND(A17="yes",AO17="yes",AQ17="HB"),1,""))


Thanks
lostinformula

 

[vandenberg p]

Hello:

Glad it worked.

Pieter Vandenberg



: ThankYou Vandenberg!!!!!

: your formula
: =IF(AND(A17="yes",AO17="Yes",AQ17="hb"),IF(D17="yes",0.5,1),"") works
: perfect!!!!

: One of my co-workers also came up with a formula that works, it a
: little longer than yours.

: =IF(AND(A17="yes",AO17="yes",AQ17="HB",D17="yes"),0.5,IF(AND(A17="yes",AO17="yes",AQ17="HB"),1,""))


: Thanks
: lostinformulas


: --
: lostinformulas
: ------------------------------------------------------------------------
: lostinformulas's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=35229
: View this thread: http://www.excelforum.com/showthread.php?threadid=550065