Matching and summing matches to distinct values for multiple rows over multiple columns with blanks

[tholder00]

My data has multiple pairs of a "name column" and "hour column" that corresponds to third value in another "movie column." The names aren't always the same in each column and can sometimes be blank or repeat. I'm trying to match and sum across all of these columns. The end goal is to have a what is shown in the picture below. I've tried to handle this through a pivot table and through index match but can't get the whole thing to come together. Thanks in advance!

 

[Eric W]

You might be able to speed up the formulas a little. This part of the A11 formula:

SUMPRODUCT(($B$2:$B$9<>"")/COUNTIF($B$2:$B$9,$B$2:$B$9&""))

just figures out how many unique movie names there are. If you put that formula in another cell, say Z1, you could replace that section of the A11 formula with Z1. Since it would only have to calculate that number once, it could speed things up. But still, what remains is still pretty computationally intensive.

I played around a bit with pivot tables, and I haven't figured anything out yet either. If I do, I'll let you know.