As I've said before you can use Sumproduct() for many things that it probably wasnt designed for. Various array functions could be used to do the same thing, but trying to do it all in sumproduct is more interesting
Lets say that we have a scenario where we have a signing in book which shows dates and people. There could be multiple people signing in on the same date. Each of these people has a role - lets say Boss, Manager and Staff, and we want to record against each entry whether there were any staff present on a day when no managers were..
Create a new sheet called guests with this information. You can leave column C blank for now, because our exercise is to create the formulas for C. You can see that we have identified row 7 as an example of a day when there were staff with no manager present.
Normally at this point we would create named ranges for all this data, but I'm going to use this example for illustration only and not use the elsewhere, so for now lets just stick to column references.
This is a tough one. We obviously can use use VLOOKUP() to translate Who into Rank.So lets do that right away to start. Put this is C2 and fill it down.
That does some of what we want, but we want C5 and C6 to say Manager (Paul was also present on 1st Feb) and Boss (Peter was present on 1st March)
So this is a tough nut to crack without some lateral thinking. Thats where SUMPRODUCT() comes in.
First lets identify the rows which don't need to be modified. Essentially thats all those where there is not "Guest" in Column B..
That gives us this
What we want to happen is that C7 looks up Staff, C5 looks up Manager, C6 looks up Boss.
Here is the formula. Put it in B2 and fill down.
Lets Break it down - we've already covered the 1st part, so lets focus on the 2nd part.