search cells based on value of another cell

[KristopMe]

What I have is a table of strings (either “yes” or “no”) which represents whether a given location in my organization turned in a particular form.

I would now like to be able to generate a report of compliance on an employee level (whether they turned it in or not)

What I would like to be able to do is put in the employee’s schedule and then have the report generate its self.

In other words I would have this.

I think that I can do this with some crazy nested if statements I just wanted to se if there was a better way to do it.

[Lupp]

@KristopMe: Would you mind to reconsider the subject of the thread? What you actually want to perform is not a search, but a special count (aiming at a ratio finally). You only assumed a search should be helpful to achieve that goal. As you may see I suggested to do it in a way not relying on a search. Actually solutions based on MATCH and COUNTIF are possible, but I would advise to use helpers then.
Or did I completely misunderstand you?

[Lupp]

That’s a kind of question where a solution is not easily worded.

You have to perform a special variant of conditional counting. As there are to combine conditions in a flexible way, SUMPRODUCT should be the function of choice.
Of course, the dimensions of all the arrays (matrices) placed in the parameter positions of SUMPRODUCT must be identical. If I understood the task correctly this will be granted for the suggested formula.

See this attached demo.

[Lupp]

By the way: I wouldn’t want to explain this to Bob, Jake, and Sara. It’s probably already too much evaluation in the world. Self-evaluation?