I have 2 data sets. First, a master table that displays and sums all of the information from the reference tables. The master table looks like this.
BayNum NumCompleted
102
103
104
105
The reference table is a running timeline with indicator variables for whether or not something was completed at various time intervals.
BayNum 1030 1100 1130 1200 1230
102 1 0 1 0 0
102 0 0 1 0 1
102 1 0 0 1 0
102 0 0 0 0 1
103 0 1 1 1 0
103 1 0 0 0 1
103 1 0 1 1 1
104 1 0 0 0 1
104 0 0 1 0 1
104 1 0 0 1 0
104 1 0 0 0 1
104 1 0 0 0 1
105 1 0 1 0 0
105 0 1 1 1 0
105 0 0 0 0 1
I would like the NumCompleted column in the master table to sum all all of the records that have the same bay number.
I think that there is some sort of sumproduct way to go about this but I don't understand arrays very well so I am having trouble visualizing how this works in my head.
I tried this formula
=SUMPRODUCT(INDEX(TPH!H2:NC166,MATCH('Post Observations'!$G$2,TPH!$F$2:$F$166,0)))
But this returns a reference error I think because Index can only work through a column instead of a full array or something. Would I have to instead do something with Index Small so that it runs through the full list of things? I've done something like that before but I don't know if that would apply here.
Per the example above, I would expect my master table to look like this.
BayNum NumCompleted
102 7
103 9
104 10
105 6
