If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
And Statement
I've looked at the below statement too many times and I can't figure out why
it won't work. I've tried using AND as well. I have 10 regions broken out by west, central, and east. I need it to pull all the regions I specify and count the number of wins. What appears to be happening is that it only pulls in the wins for the first region listed, North East and doesn't count the remaining. =(Sum(IIf([Status]="wins" And [Region]="north east" Or "new england" Or "Great Lakes" Or "North East",1,0))) I've tried the below as well but no luck: =(Sum(IIf([Status]="wins" And [Region]="north east" And [Region]= "new england" And [Region]= "Great Lakes" And [Region]= "North East",1,0))) I hate to post questions but I do give it my best try before coming to you all. I admit I have some trouble with the complicated formulas. Thanks for your time and help! |
#2
|
|||
|
|||
Try this
=Sum(IIf([Status]="wins" And ([Region]="north east" Or "new england" Or "Great Lakes" Or "North East"),1,0)) -- In God We Trust - Everything Else We Test "_" wrote: I've looked at the below statement too many times and I can't figure out why it won't work. I've tried using AND as well. I have 10 regions broken out by west, central, and east. I need it to pull all the regions I specify and count the number of wins. What appears to be happening is that it only pulls in the wins for the first region listed, North East and doesn't count the remaining. =(Sum(IIf([Status]="wins" And [Region]="north east" Or "new england" Or "Great Lakes" Or "North East",1,0))) I've tried the below as well but no luck: =(Sum(IIf([Status]="wins" And [Region]="north east" And [Region]= "new england" And [Region]= "Great Lakes" And [Region]= "North East",1,0))) I hate to post questions but I do give it my best try before coming to you all. I admit I have some trouble with the complicated formulas. Thanks for your time and help! |
#3
|
|||
|
|||
It appears that it adds anything with a status of "wins" but doesn't test for
region. B/c I get a total of 19 (and that happens to be the total of all 10 regions) not just the ones specified below. Any more ideas? Thanks! I am still trying as well. "Ofer" wrote: Try this =Sum(IIf([Status]="wins" And ([Region]="north east" Or "new england" Or "Great Lakes" Or "North East"),1,0)) -- In God We Trust - Everything Else We Test "_" wrote: I've looked at the below statement too many times and I can't figure out why it won't work. I've tried using AND as well. I have 10 regions broken out by west, central, and east. I need it to pull all the regions I specify and count the number of wins. What appears to be happening is that it only pulls in the wins for the first region listed, North East and doesn't count the remaining. =(Sum(IIf([Status]="wins" And [Region]="north east" Or "new england" Or "Great Lakes" Or "North East",1,0))) I've tried the below as well but no luck: =(Sum(IIf([Status]="wins" And [Region]="north east" And [Region]= "new england" And [Region]= "Great Lakes" And [Region]= "North East",1,0))) I hate to post questions but I do give it my best try before coming to you all. I admit I have some trouble with the complicated formulas. Thanks for your time and help! |
#4
|
|||
|
|||
Won't work that way either.
=Sum(IIf([Status]="wins" And ([Region]="north east" Or [Region]="new england" Or [Region]="Great Lakes" Or [Region]="North East"),1,0)) or possibly =Sum(IIf([Status]="wins" And [Region] IN ("north east","new england","Great Lakes", "North East"),1,0)) -- Duane Hookom MS Access MVP -- "Ofer" wrote in message ... Try this =Sum(IIf([Status]="wins" And ([Region]="north east" Or "new england" Or "Great Lakes" Or "North East"),1,0)) -- In God We Trust - Everything Else We Test "_" wrote: I've looked at the below statement too many times and I can't figure out why it won't work. I've tried using AND as well. I have 10 regions broken out by west, central, and east. I need it to pull all the regions I specify and count the number of wins. What appears to be happening is that it only pulls in the wins for the first region listed, North East and doesn't count the remaining. =(Sum(IIf([Status]="wins" And [Region]="north east" Or "new england" Or "Great Lakes" Or "North East",1,0))) I've tried the below as well but no luck: =(Sum(IIf([Status]="wins" And [Region]="north east" And [Region]= "new england" And [Region]= "Great Lakes" And [Region]= "North East",1,0))) I hate to post questions but I do give it my best try before coming to you all. I admit I have some trouble with the complicated formulas. Thanks for your time and help! |
#5
|
|||
|
|||
Thanks - I tried the first one and it worked!
"Duane Hookom" wrote: Won't work that way either. =Sum(IIf([Status]="wins" And ([Region]="north east" Or [Region]="new england" Or [Region]="Great Lakes" Or [Region]="North East"),1,0)) or possibly =Sum(IIf([Status]="wins" And [Region] IN ("north east","new england","Great Lakes", "North East"),1,0)) -- Duane Hookom MS Access MVP -- "Ofer" wrote in message ... Try this =Sum(IIf([Status]="wins" And ([Region]="north east" Or "new england" Or "Great Lakes" Or "North East"),1,0)) -- In God We Trust - Everything Else We Test "_" wrote: I've looked at the below statement too many times and I can't figure out why it won't work. I've tried using AND as well. I have 10 regions broken out by west, central, and east. I need it to pull all the regions I specify and count the number of wins. What appears to be happening is that it only pulls in the wins for the first region listed, North East and doesn't count the remaining. =(Sum(IIf([Status]="wins" And [Region]="north east" Or "new england" Or "Great Lakes" Or "North East",1,0))) I've tried the below as well but no luck: =(Sum(IIf([Status]="wins" And [Region]="north east" And [Region]= "new england" And [Region]= "Great Lakes" And [Region]= "North East",1,0))) I hate to post questions but I do give it my best try before coming to you all. I admit I have some trouble with the complicated formulas. Thanks for your time and help! |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Do I need a sumif or sum of a vlookup formula? | PeterB | Worksheet Functions | 0 | June 1st, 2005 12:23 PM |
Stop Statement Doesn't Stop | drwip | General Discussion | 1 | December 2nd, 2004 11:10 PM |
Using Hyperlinks in Mail Merge IF...THEN...ELSE Statements | Mark V | Mailmerge | 8 | November 30th, 2004 01:31 PM |
using the results of a SQL SELECT(COUNT) statement in VBA | Paul James | Running & Setting Up Queries | 7 | September 24th, 2004 09:44 PM |
Access 2000 query SQL statement into VBA code | Clint | Running & Setting Up Queries | 1 | June 10th, 2004 01:33 PM |