Comparing Lists and Sum on Matches

[jakeman]

Hello - so I am working with two lists of data that I would like to compare. One list is called PCP_Selections and the other is called PCP_Data. PCP_Selections is a much smaller list and may have anywhere from 1 - 7 names in that list. In the PCP_Data list, there are over 2000 rows and it has two other columns that are relevant to me: Date and Member Total.

What I would like to do is compare the PCP_Selections list to the PCP_Data list and where there is a match on the name, I would then like to sum the Member Totals for a given period of time. So for example, in my PCP_Selections list, I have 5 names and I am interested in seeing what the total number of Members were for Q1 2013.

Hope I explained myself clearly.

 

[tybaltlives]

maybe something like this...

Excel 2010
	A	B	C	D	E	F	G	H	I
1	PCP_Selections						PCP_Data
2	Name	Q1	Q2	Q3	Q4		Name	Date	Tot
3	Name1	0	132	175	87		Name4	Q3	98
4	Name2	0	130	162	76		Name4	Q1	61
5	Name3	64	51	0	270		Name4	Q1	57
6	Name4	218	78	172	0		Name5	Q4	91
7	Name5	134	87	191	91		Name4	Q2	78
8	Name6	0	0	0	151		Name4	Q1	100
9							Name1	Q4	87
10							Name6	Q4	57
11							Name2	Q2	72
12							Name6	Q4	94
13							Name3	Q4	89
14							Name2	Q4	76
15							Name3	Q4	85
16							Name3	Q4	96
17							Name4	Q3	74
18							Name5	Q3	67
19							Name1	Q3	81
20							Name5	Q1	60
21							Name3	Q1	64
22							Name1	Q2	66
23							Name1	Q3	94
24							Name2	Q3	79
25							Name5	Q3	65
26							Name2	Q2	58
27							Name3	Q2	51
28							Name5	Q2	87
29							Name1	Q2	66
30							Name5	Q1	74
31							Name5	Q3	59
32							Name2	Q3	83
Sheet1

Cell Formulas
Range	Formula
B3	=SUMIFS($I$3:$I$32,($G$3:$G$32),$A3,($H$3:$H$32),B$2)
B4	=SUMIFS($I$3:$I$32,($G$3:$G$32),$A4,($H$3:$H$32),B$2)
B5	=SUMIFS($I$3:$I$32,($G$3:$G$32),$A5,($H$3:$H$32),B$2)
B6	=SUMIFS($I$3:$I$32,($G$3:$G$32),$A6,($H$3:$H$32),B$2)
B7	=SUMIFS($I$3:$I$32,($G$3:$G$32),$A7,($H$3:$H$32),B$2)
B8	=SUMIFS($I$3:$I$32,($G$3:$G$32),$A8,($H$3:$H$32),B$2)
C3	=SUMIFS($I$3:$I$32,($G$3:$G$32),$A3,($H$3:$H$32),C$2)
C4	=SUMIFS($I$3:$I$32,($G$3:$G$32),$A4,($H$3:$H$32),C$2)
C5	=SUMIFS($I$3:$I$32,($G$3:$G$32),$A5,($H$3:$H$32),C$2)
C6	=SUMIFS($I$3:$I$32,($G$3:$G$32),$A6,($H$3:$H$32),C$2)
C7	=SUMIFS($I$3:$I$32,($G$3:$G$32),$A7,($H$3:$H$32),C$2)
C8	=SUMIFS($I$3:$I$32,($G$3:$G$32),$A8,($H$3:$H$32),C$2)
D3	=SUMIFS($I$3:$I$32,($G$3:$G$32),$A3,($H$3:$H$32),D$2)
D4	=SUMIFS($I$3:$I$32,($G$3:$G$32),$A4,($H$3:$H$32),D$2)
D5	=SUMIFS($I$3:$I$32,($G$3:$G$32),$A5,($H$3:$H$32),D$2)
D6	=SUMIFS($I$3:$I$32,($G$3:$G$32),$A6,($H$3:$H$32),D$2)
D7	=SUMIFS($I$3:$I$32,($G$3:$G$32),$A7,($H$3:$H$32),D$2)
D8	=SUMIFS($I$3:$I$32,($G$3:$G$32),$A8,($H$3:$H$32),D$2)
E3	=SUMIFS($I$3:$I$32,($G$3:$G$32),$A3,($H$3:$H$32),E$2)
E4	=SUMIFS($I$3:$I$32,($G$3:$G$32),$A4,($H$3:$H$32),E$2)
E5	=SUMIFS($I$3:$I$32,($G$3:$G$32),$A5,($H$3:$H$32),E$2)
E6	=SUMIFS($I$3:$I$32,($G$3:$G$32),$A6,($H$3:$H$32),E$2)
E7	=SUMIFS($I$3:$I$32,($G$3:$G$32),$A7,($H$3:$H$32),E$2)
E8	=SUMIFS($I$3:$I$32,($G$3:$G$32),$A8,($H$3:$H$32),E$2)

 

[jakeman]

I think that might work. Gotta check it out. Thanks for the solution, man...I really appreciate that.

 

[tybaltlives]

good luck with it. Note that its a single sumifs formula that you can copy throughout your reporting table. So once you set the first formula up its pretty easy. Just note the placement of the $ signs that freeze the cell references.

-R