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
|
|||
|
|||
"OR" in a array
=SUM(IF((FIXEDm!$F$2:$F$5000="WG1")*OR((LEFT(FIXED m!$I$2:$I$5000,4)="2q13"),(LEFT(FIXEDm!$I$2:$I$500 0,4)="2q15"),(LEFT(FIXEDm!$I$2:$I$5000,4)="2q17")) ,1,0))
I am trying to SUM in an array using "or" I just want to count the folks in work group 1 (WG1) that also have a job code with the left 4 Characters 2q13 or 2q15 or 2q17. When this runs I think it counts everyone in work group 1. ( first try using "OR") Works OK if I ... add the separate SUM statements together but gets quite long. Any help on "OR"? Thanks in advance. Wildman |
#2
|
|||
|
|||
"wildman" wrote...
=SUM(IF((FIXEDm!$F$2:$F$5000="WG1")*OR((LEFT(FIXE Dm!$I$2:$I$5000,4)="2q13"), (LEFT(FIXEDm!$I$2:$I$5000,4)="2q15"), (LEFT(FIXEDm!$I$2:$I$5000,4)="2q17")),1,0)) ... Try =SUMPRODUCT((FIXEDm!$F$2:$F$5000="WG1") *ISNUMBER(FIND("|"&LEFT(FIXEDm!$I$2:$I$5000,4)&"|" ,"|2q13|2q15|2q17|"))) -- Never attach files. Snip unnecessary quoted text. Never multipost (though crossposting is usually OK). Don't change subject lines because it corrupts Google newsgroup archives. |
#3
|
|||
|
|||
"OR" in a array
Try this non array formula
=SUMPRODUCT((F2:F25="WG1")*((LEFT(I2:I25,4)="2q13" )+(LEFT(I2:I25,4)="2q15")+ (LEFT(I2:I25,4)="2q17"))) all in one line adjust the range to suit you every range should be equal in size Cecil "wildman" wrote in message ... =SUM(IF((FIXEDm!$F$2:$F$5000="WG1")*OR((LEFT(FIXED m!$I$2:$I$5000,4)="2q13"), (LEFT(FIXEDm!$I$2:$I$5000,4)="2q15"),(LEFT(FIXEDm! $I$2:$I$5000,4)="2q17")),1 ,0)) I am trying to SUM in an array using "or" I just want to count the folks in work group 1 (WG1) that also have a job code with the left 4 Characters 2q13 or 2q15 or 2q17. When this runs I think it counts everyone in work group 1. ( first try using "OR") Works OK if I ... add the separate SUM statements together but gets quite long. Any help on "OR"? Thanks in advance. Wildman |
#4
|
|||
|
|||
"OR" in a array
You can compact that formula a bit by using something like:
+sumproduct((f2:f25="WG1")*(left(g2:g25,4)={"2q13" ,"2q15","2q17})) "Cecilkumara Fernando" wrote in message ... Try this non array formula =SUMPRODUCT((F2:F25="WG1")*((LEFT(I2:I25,4)="2q13" )+(LEFT(I2:I25,4)="2q15")+ (LEFT(I2:I25,4)="2q17"))) all in one line adjust the range to suit you every range should be equal in size Cecil "wildman" wrote in message ... =SUM(IF((FIXEDm!$F$2:$F$5000="WG1")*OR((LEFT(FIXED m!$I$2:$I$5000,4)="2q13"), (LEFT(FIXEDm!$I$2:$I$5000,4)="2q15"),(LEFT(FIXEDm! $I$2:$I$5000,4)="2q17")),1 ,0)) I am trying to SUM in an array using "or" I just want to count the folks in work group 1 (WG1) that also have a job code with the left 4 Characters 2q13 or 2q15 or 2q17. When this runs I think it counts everyone in work group 1. ( first try using "OR") Works OK if I ... add the separate SUM statements together but gets quite long. Any help on "OR"? Thanks in advance. Wildman |
Thread Tools | |
Display Modes | |
|
|