There might be—probably is—a clever way to do this with worksheet functions. As I get more used to VBA I tend to go there quicker rather than spend time trying to figure out how to do it with formulae. So let me ask this first: Is this something you want to repeatedly, say once a week, with different data each time? If so, rather than set up clever formulae maybe a VBA macro would serve you better.

And it does have the advantage that I know how I'd go about it . But maybe someone else here will think up a non-VBA solution for you.