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!

 

[Fluff]

Hi & welcome to MrExcel
How abut
=SUMPRODUCT(($B$2:$F$4=$A8)*($A$2:$A$4=B$7),$C$2:$G$4)

 

[tholder00]

Hi & welcome to MrExcel
How abut
=SUMPRODUCT(($B$2:$F$4=$A8)*($A$2:$A$4=B$7),$C$2:$G$4)

Perfect - thanks so much. This is so much more simple than the path I was going down!

 

[Fluff]

You're welcome & thanks for the feedback

 

[tholder00]

The data is more complicated than I originally posted. There's another level to split everything out on a weekly basis. Here's an example of what the raw data looks like and how the final data needs to be organized. In a way, if the three staff/hours columns could be stacked a pivot table could probably handle this. The data is coming from an online source so I'd like to avoid any manual manipulation and have it all done through formulas/pivot tables that I setup. Thanks in advance!

 

[Fluff]

How about

Book1
	A	B	C	D	E	F	G	H
1		Movie	S1	h	s2	h	s3	h
2	01/01/2000	A	daffy	1	minnie	2	pluto	3
3	08/01/2000	B	mickey	1			mickey	3
4	16/01/2000	C	pluto	1	daffy	2	mickey	3
5	01/01/2000	B	daffy	1	minnie	2	pluto	3
6	08/01/2000	C	mickey	1			mickey	3
7
8
9
10			01/01/2000	08/01/2000	16/01/2000
11	daffy	A	1	0	0
12	daffy	B	1	0	0
13	daffy	C	0	0	2
14	mickey	A	0	0	0
15	mickey	B	0	4	0
16	mickey	C	0	4	3
17	Pluto	A	3	0	0
18	Pluto	B	3	0	0
19	Pluto	C	0	0	1
20	minnie	A	2	0	0
21	minnie	B	2	0	0
22	minnie	C	0	0	0
Log

Cell Formulas
Range	Formula
C11	=SUMPRODUCT(($C$2:$G$6=$A11)*($B$2:$B$6=$B11)*($A$2:$A$6=C$10),$D$2:$H$6)

 

[tholder00]

Okay that is great! Is there a method to automatically create the list of characters and movies (A11:B22)? As more data is added, more characters and movies will be added too.

 

[Fluff]

Probably, but that's beyond my meager skills with formulae.

 

[Eric W]

In A11:

=IFERROR(IF(OR(A10=[COLOR=#ff0000]""[/COLOR],COUNTIF(A$10:A10,A10)=SUMPRODUCT(($B$2:$B$9<>"")/COUNTIF($B$2:$B$9,$B$2:$B$9&""))),INDIRECT(TEXT(SMALL(IF(ISERROR($C$2:$G$9+0),IF(COUNTIF($A$10:$A10,$C$2:$G$9)=0,ROW($C$2:$G$9)*1000+COLUMN($C$2:$G$9))),1),"R000C000"),0),A10),"")

confirmed with Control+Shift+Enter. This assumes that A10 is empty. If there's a heading there, just put it in the red quotes.

In B11:

=IF(A11="","",INDEX($B$2:$B$9,SMALL(IF($B$2:$B$9<>"",IF(MATCH($B$2:$B$9,$B$2:$B$9,0)=ROW($B$2:$B$9)-ROW($B$2)+1,ROW($B$2:$B$9)-ROW($B$2)+1)),COUNTIF($A$11:$A11,A11))))

Also with Control+Shift+Enter.

These are pretty intensive formulas, so I don't know how well they'll perform with a lot of data.

 

[tholder00]

Thank you very much, this did work! It does start to bog down, like you thought. Would there be anyway to handle all of this through a pivot table? It seems like there should be a way to, but I just can't figure it out.