SUMPRODUCT with multiple OR

[WildWill]

Hi

I am trying to modify this SUMPRODUCT formula so that the last component:*('Staffing Data'!$AB$8:$AB$9082={"Active - Off Site","Active - Site Based"}) will search for any of three conditions and not just two, as indicated here. I have tried adding the third condition but it does not seem to work. How would I modify this to count occurences of "Active - Off Site", "Active - On Site" OR "Active - Training})? Here is the full sum:


=SUMPRODUCT(('Staffing Data'!$S$8:$S$9082="Unskilled - Defined Decision-making")*('Staffing Data'!$K$8:$K$9082="Male")*('Staffing Data'!$L$8:$L$9082="Black")*('Staffing Data'!$AB$8:$AB$9082={"Active - Off Site","Active - Site Based"}))

 

[shg]

Try this:

=SUMPRODUCT(('Staffing Data'!$S$8:$S$9082="Unskilled - Defined Decision-making")
           *('Staffing Data'!$K$8:$K$9082="Male")
           *('Staffing Data'!$L$8:$L$9082="Black")
           *('Staffing Data'!$AB$8:$AB$9082={"Active - Off Site","Active - Site Based","Active - Training}))

 

[LxQ]

it worked for me...
=SUMPRODUCT(('Staffing Data'!$S$8:$S$9082="Unskilled - Defined Decision-making")*('Staffing Data'!$K$8:$K$9082="Male")*('Staffing Data'!$L$8:$L$9082="Black")*('Staffing Data'!$AB$8:$AB$9082={"Active - Off Site","Active - Site Based","Active - Training"}))

 

[T. Valko]

Hi

I am trying to modify this SUMPRODUCT formula so that the last component:*('Staffing Data'!$AB$8:$AB$9082={"Active - Off Site","Active - Site Based"}) will search for any of three conditions and not just two, as indicated here. I have tried adding the third condition but it does not seem to work. How would I modify this to count occurences of "Active - Off Site", "Active - On Site" OR "Active - Training})? Here is the full sum:


=SUMPRODUCT(('Staffing Data'!$S$8:$S$9082="Unskilled - Defined Decision-making")*('Staffing Data'!$K$8:$K$9082="Male")*('Staffing Data'!$L$8:$L$9082="Black")*('Staffing Data'!$AB$8:$AB$9082={"Active - Off Site","Active - Site Based"}))

It'd be better if you used cells to hold the criteria.

A1 = Unskilled - Defined Decision-making
B1 = Male
C1 = Black
D1 = Active - Off Site
D2 = Active - Site Based
D3 = Active - Training

Then:

=SUMPRODUCT(--('Staffing Data'!$S$8:$S$9082=A1),--('Staffing Data'!$K$8:$K$9082=B1),--('Staffing Data'!$L$8:$L$9082=C1),--(ISNUMBER(MATCH('Staffing Data'!$AB$8:$AB$9082,D1:D3,0))))