SUMPRODUCT with multiple values referenced

[Darren_workforce]

Hello,

I have a sheet in which B4, E4, H4 or K4 could potentially have a specific job title (trainer, auditor, scheduler) and if the adjacent cell (A4, D4, G4 or J4) is not blank, we are to assume the data in Column A refers to the person occupying the job. It then would tally up the references of all four cells and generate the total number of filled positions. Using SUMPRODUCT for the first time, I got so far as to figure out a single reference formula

=SUMPRODUCT(--(B:B="trainer")*(A:A<>""))
=SUMPRODUCT(--(B:B="auditor")*(A:A<>""))
=SUMPRODUCT(--(B:B="scheduler")*(A:A<>""))

However, I'm not familiar with how to apply the logic to search for all 3 jobs titles in a single formula. I anticipate I will be expanding the formula for other jobs in other cell locations so I'd like to figure out how to combine these three.

Many thanks in advance for all help provided.

 

[awoohaw]

if you are looking for "OR" any of those jobs this may work:

=SUMPRODUCT(
(--(B:B="trainer")*(A:A<>""))+
(--(B:B="auditor")*(A:A<>""))+
(--(B:B="scheduler")*(A:A<>""))+
)

 

[Darren_workforce]

if you are looking for "OR" any of those jobs this may work:

=SUMPRODUCT(
(--(B:B="trainer")*(A:A<>""))+
(--(B:B="auditor")*(A:A<>""))+
(--(B:B="scheduler")*(A:A<>""))+
)

Thank you awoohaw! That did exactly what I needed!!

 

[awoohaw]

My pleasure, I'm happy you found a solution at the Mr. Excel Forum.

Best Wishes!