"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

"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.

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

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