SUMPRODUCT or SUMIF or something else?

[samilynn]

I have a sheet with salereps which are assigned to two teams. How do I replace my simple formula (in rows 20 and 21) with some sort of a lookup, or sumif, which basically says "lookup the rep names in a table, and total their sales as a team?" (the teams are on a different workbook, but I put them on the same sheet in this example for simplicity):

Book1.xlsx
	A	B	C	D	E
1	SLSREP	JAN SALES	FEB SALES	MAR SALES	APR SALES
2	REP1	$463,894	$733,811	$1,064,166	$1,348,145
3	REP2	$28,104	$33,301	$65,012	$73,504
4	REP3	$446,867	$682,103	$885,842	$371,260
5	REP4	$688,287	$1,099,895	$1,715,446	$2,250,357
6	REP55	$536,881	$443,793	$679,629	$838,596
7	REP66	$117,756	$681,703	$359,723	$37,412
8	REP77	$564,908	$936,422	$1,496,989	$1,945,976
9	REP88	$643,928	$925,184	$1,419,050	$1,879,872
10
11
12
13	TEAM1	TEAM2
14	REP1	REP3
15	REP2	REP4
16	REP66	REP55
17	REP88	REP77
18
19
20	TEAM1 TOTAL	$1,253,683	$2,373,999	$2,907,951	$3,338,934
21	TEAM 2 TOTAL	$2,236,944	$3,162,214	$4,777,906	$5,406,188
Sheet1

Cell Formulas
Range	Formula
B20:E20	B20	=B2+B3+B7+B9
B21:E21	B21	=B4+B5+B6+B8

 

[Fluff]

How about

=SUMPRODUCT(SUMIFS(B$2:B$9,$A$2:$A$9,$A$14:$A$17))

 

[samilynn]

Perfect, thank you!!

 

[Fluff]

You're welcome & thanks for the feedback.