Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF,And,OR Help
I need help adding another condition to my formula.I need AE9 to = "Epress" or ="EXPU". At this time it only returns a response for ="Express" =IF(AND(J9="yes",AK9="yes",AE9="Express"),1,"") I also need this formula to total the column. AE9:AE200. -- lostinformulas ------------------------------------------------------------------------ lostinformulas's Profile: http://www.excelforum.com/member.php...o&userid=35229 View this thread: http://www.excelforum.com/showthread...hreadid=550065 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF,And,OR Help
try
=IF(AND(J9="yes",AK9="yes",or(AE9="Express",AE9="E press",AE9="EXPU")),sum(AE9:AE200),"") Are you sure on summiing the AE9 since you are looking for text in AE9? "lostinformulas" wrote: I need help adding another condition to my formula.I need AE9 to = "Epress" or ="EXPU". At this time it only returns a response for ="Express" =IF(AND(J9="yes",AK9="yes",AE9="Express"),1,"") I also need this formula to total the column. AE9:AE200. -- lostinformulas ------------------------------------------------------------------------ lostinformulas's Profile: http://www.excelforum.com/member.php...o&userid=35229 View this thread: http://www.excelforum.com/showthread...hreadid=550065 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF,And,OR Help
=IF(AND(J9="yes",AK9="yes",OR(AE9="Express",AE9="E XPU")),SUM(AE9:AE200),"") Note your SUM will "include" the word EXPRESS or EXPU ... will still work. HTH "lostinformulas" wrote: I need help adding another condition to my formula.I need AE9 to = "Epress" or ="EXPU". At this time it only returns a response for ="Express" =IF(AND(J9="yes",AK9="yes",AE9="Express"),1,"") I also need this formula to total the column. AE9:AE200. -- lostinformulas ------------------------------------------------------------------------ lostinformulas's Profile: http://www.excelforum.com/member.php...o&userid=35229 View this thread: http://www.excelforum.com/showthread...hreadid=550065 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF,And,OR Help
=IF(AND(J9="yes",AK9="yes",(OR(AE9="express", AE9="epress", AE9="EXPU"))),SUM(AE9:AE200),"") -- Bearacade ------------------------------------------------------------------------ Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016 View this thread: http://www.excelforum.com/showthread...hreadid=550065 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF,And,OR Help
I guess I don't need a sum but need to count the times that either Express or EXPU appears between AE9:AE200 -- lostinformulas ------------------------------------------------------------------------ lostinformulas's Profile: http://www.excelforum.com/member.php...o&userid=35229 View this thread: http://www.excelforum.com/showthread...hreadid=550065 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF,And,OR Help
=countif(AE9:AE200,"=Express")+countif(AE9:AE200," =EXPU")
"lostinformulas" wrote: I guess I don't need a sum but need to count the times that either Express or EXPU appears between AE9:AE200 -- lostinformulas ------------------------------------------------------------------------ lostinformulas's Profile: http://www.excelforum.com/member.php...o&userid=35229 View this thread: http://www.excelforum.com/showthread...hreadid=550065 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF,And,OR Help
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),"") -- Bearacade ------------------------------------------------------------------------ Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016 View this thread: http://www.excelforum.com/showthread...hreadid=550065 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF,And,OR Help
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 -- lostinformulas ------------------------------------------------------------------------ lostinformulas's Profile: http://www.excelforum.com/member.php...o&userid=35229 View this thread: http://www.excelforum.com/showthread...hreadid=550065 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF,And,OR Help
=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() -- Bearacade ------------------------------------------------------------------------ Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016 View this thread: http://www.excelforum.com/showthread...hreadid=550065 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF,And,OR Help
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...o&userid=35229 View this thread: http://www.excelforum.com/showthread...hreadid=550065 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF,And,OR Help
Hello:
If I understand your question this should do it: =IF(AND(A17="yes",AO17="Yes",AQ17="hb"),IF(D17="ye s",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 lostinformulas <lostinformulas.29595d_1149871804.0547@excelforu m-nospam.com wrote: : 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...o&userid=35229 : View this thread: http://www.excelforum.com/showthread...hreadid=550065 |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF,And,OR Help
ThankYou Vandenberg!!!!! your formula =IF(AND(A17="yes",AO17="Yes",AQ17="hb"),IF(D17="ye s",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...o&userid=35229 View this thread: http://www.excelforum.com/showthread...hreadid=550065 |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF,And,OR Help
Hello:
Glad it worked. Pieter Vandenberg lostinformulas <lostinformulas.295hhc_1149882603.1263@excelforu m-nospam.com wrote: : ThankYou Vandenberg!!!!! : your formula : =IF(AND(A17="yes",AO17="Yes",AQ17="hb"),IF(D17="ye s",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...o&userid=35229 : View this thread: http://www.excelforum.com/showthread...hreadid=550065 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|