Which function should I use??

[BELFRED6]

In table A1:E10, I have a list of stores (listed with their IDs) and items they order.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I have three drivers to deliver those stores. Driver 1 delivers stores with a 100-type of ID (from 100 to 199). Driver 2 delivers stores with a 200-type of ID (from 200 to 299), and so on…<o></o>
In a separate table (the second table below), I want to get the SUM (TOTAL) of each type of item delivered by each driver.
The table below should clarify what I mean here. I need a formula in yellow cells. Right now I just entered figures manually. I am not sure about which function could do the job. Any suggestion that could help me out?
<o></o>
<o> </o>
<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">ID #</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Store</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Item 1</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Item 2</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Item 3</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">101</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid">Store 1</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">1</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">2</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid"> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">102</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid">Store 2</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid"> </TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">3</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid"> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">180</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid">Store 3</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">1</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid"> </TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">2</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">201</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid">Store 4</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid"> </TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid"> </TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid"> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">205</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid">Store 5</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">2</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">4</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">245</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid">Store 6</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid"> </TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid"> </TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">3</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">310</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid">Store 7</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">5</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">1</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid"> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">350</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid">Store 8</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">2</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid"> </TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid"> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">351</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid">Store 9</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">1</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid"> </TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid"> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="BORDER-BOTTOM: #000000 1px solid"> </TD><TD style="BORDER-BOTTOM: #000000 1px solid"> </TD><TD style="BORDER-BOTTOM: #000000 1px solid"> </TD><TD style="BORDER-BOTTOM: #000000 1px solid"> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid">TOTALS</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Driver 1</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Driver 2</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Driver 3</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid">Item 1</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">2</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">2</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">8</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid">Item 2</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">5</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">4</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">1</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid">Item 3</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">2</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">4</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">0</TD><TD> </TD></TR></TBODY></TABLE>

 

[jbeaucaire]

This shows the first formula for each row, copy them across after entering.

Excel Workbook
	A	B	C	D	E
1	ID #	Store	Item 1	Item 2	Item 3
2	101	Store 1	1	2
3	102	Store 2		3
4	180	Store 3	1		2
5	201	Store 4
6	205	Store 5	2	4	1
7	245	Store 6			3
8	310	Store 7	5	1
9	350	Store 8	2
10	351	Store 9	1
11
12
13	TOTALS	Driver 1	Driver 2	Driver 3
14	Item 1	2	2	8
15	Item 2	5	4	1
16	Item 3	2	4	0
Sheet1

 

[Barry Katcher]

Use the =LEFT() formulas in new column F below, and then the =SUMIF() formuals in the table

Book1
	A	B	C	D	E	F
1	ID #	Store	Item 1	Item 2	Item 3	Short ID
2	101	Store 1	1	2		1
3	102	Store 2		3		1
4	180	Store 3	1		2	1
5	201	Store 4				2
6	205	Store 5	2	4	1	2
7	245	Store 6			3	2
8	310	Store 7	5	1		3
9	350	Store 8	2			3
10	351	Store 9	1			3
11
12
13	TOTALS	Driver 1	Driver 2	Driver 3
14	Item 1	2	2	8
15	Item 2	5	4	1
16	Item 3	2	4	0
Sheet1

 

[stanleydgromjr]

BELFRED6,

Excel Workbook
	A	B	C	D	E
1	ID #	Store	Item 1	Item 2	Item 3
2	101	Store 1	1	2
3	102	Store 2		3
4	180	Store 3	1		2
5	201	Store 4
6	205	Store 5	2	4	1
7	245	Store 6			3
8	310	Store 7	5	1
9	350	Store 8	2
10	351	Store 9	1
11
12
13	TOTALS	Driver 1	Driver 2	Driver 3
14	Item 1	2	2	8
15	Item 2	5	4	1
16	Item 3	2	4	0
Sheet1

Have a great day,
Stan

 

[BELFRED6]

ADVERTISEMENT

Thank you very much, all of you! You've been of great help.

 

[Barry Katcher]

Sorry it took so long.

 

[jbeaucaire]

One more option on top of the others:

=SUMPRODUCT(--(FLOOR($A$2:$A$10,100)/100=COLUMN()-1),$C$2:$C$10)
=SUMPRODUCT(--(FLOOR($A$2:$A$10,100)/100=COLUMN()-1),$D$2:$D$10)
=SUMPRODUCT(--(FLOOR($A$2:$A$10,100)/100=COLUMN()-1),$E$2:$E$10)
...copied across.