Comparing Lists and Sum on Matches

jakeman

Active Member
Joined
Apr 29, 2008
Messages
313
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.
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

tybaltlives

Active Member
Joined
Nov 6, 2006
Messages
414
maybe something like this...


Excel 2010
ABCDEFGHI
1PCP_SelectionsPCP_Data
2NameQ1Q2Q3Q4NameDateTot
3Name1013217587Name4Q398
4Name2013016276Name4Q161
5Name364510270Name4Q157
6Name4218781720Name5Q491
7Name51348719191Name4Q278
8Name6000151Name4Q1100
9Name1Q487
10Name6Q457
11Name2Q272
12Name6Q494
13Name3Q489
14Name2Q476
15Name3Q485
16Name3Q496
17Name4Q374
18Name5Q367
19Name1Q381
20Name5Q160
21Name3Q164
22Name1Q266
23Name1Q394
24Name2Q379
25Name5Q365
26Name2Q258
27Name3Q251
28Name5Q287
29Name1Q266
30Name5Q174
31Name5Q359
32Name2Q383
Sheet1
Cell Formulas
RangeFormula
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

Active Member
Joined
Apr 29, 2008
Messages
313
I think that might work. Gotta check it out. Thanks for the solution, man...I really appreciate that.
 

tybaltlives

Active Member
Joined
Nov 6, 2006
Messages
414
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
 

Forum statistics

Threads
1,172,169
Messages
5,879,442
Members
433,431
Latest member
LikeablePringle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top