"OR" in a array

[wildman]

=SUM(IF((FIXEDm!$F$2:$F$5000="WG1")*OR((LEFT(FIXEDm!$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

 

[Harlan Grove]

=SUM(IF((FIXEDm!$F$2:$F$5000="WG1")*OR((LEFT(FIXEDm!$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|")))

 

[Cecilkumara Fernando]

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

 

[Dave]

You can compact that formula a bit by using something like:

+sumproduct((f2:f25="WG1")*(left(g2:g25,4)={"2q13","2q15","2q17}))