Formula Help

[Emmily]

Hi, how can the sumproduct total be adjusted to only show the "TOTAL" for each manager. For example for Bill Gates and Ian wright the formula is doubling the figures, i only want the formula to pickup the TOTAL of "No. of Items." for each manager

Excel Workbook
	A	B	C	D
3	JAMES BOND	932
4	BILL GATES	24
5	IAN WRIGHT	4
6
7			2-5	6-29
8	Manager	Team	No. of items	No. of items
9	Joe Plumber	Test1	12	323
10	Joe Plumber	Test2	14	154
11	Joe Plumber	Test3	16	65
12	Joe Plumber	Test4	32	98
13	Joe Plumber	Test5	14	74
14	Joe Plumber	Test6	18	15
15	Joe Plumber	Test7	65	32
16	JAMES BOND	TOTAL	171	761
17
18	Bill Gates	Test9	12	0
19	BILL GATES	TOTAL	12	0
20
21	Ian Wright	Test10	0	2
22	IAN WRIGHT	TOTAL	0	2
23
24		TOTAL	183	763
25
26
Sheet1

 

[Marcelo Branco]

Emily,

I'm not understanding why JAMES BOND in row 16...

If the correct is JOE PLUMBER (also in A3) maybe

=SUMPRODUCT(($A$9:$A$23=A3)*($B$9:$B$23="TOTAL")*$C$9:$D$23)


M.

 

[ukmikeb]

Hi

Try in B3 -

=SUMPRODUCT(($A$9:$A$23=A3)*($B$9:$B$23="TOTAL")*$C$9:$D$23)

and copied down.

hth