What formula to use???

[doms123]

Hi I am using excel 2007 and i am having problem on what formula to use on my third sheet...

my first sheet is my Main Sheet where I input data then, a button for encoding.

Excel Workbook
	A	B	C	D	E	F
1	*	*	*	*	*	*
2	*	poducts	*			*
3	*	*	*	*	*	*
4	*	date delivered	*	*	*	*
5	*	*	month	Date	Year	*
6	*	location of delivery	*			*
7	*	*	*	*	*	*
8	*	delivered by	*			*
9	*	*	*	*	*	*
10	*	sales	*			*
11	*	*	*	*	*	*
12	*	*	Encode		*	*
13	*	*	*	*	*	*
MAIN Sheet

then after encoding the data will be encoded on my masterlist, second sheet

Excel Workbook
	A	B	C	D	E	F	G
1	Products	Date Delivered			Location of Delivery	Delivered by	Sales
2		Month	Day	Year
3	Shoes1	June	8	2010	Area 1	Boy 7	1k
4	Shoes2	December	17	2010	Area 2	Boy 2	1k
5	Shoes3	August	7	2010	Area 3	Boy 3	1k
6	Undrewears1	September	5	2010	Area 5	Boy 22	1k
7	Undrewears2	October	5	2010	Area 5	Boy 5	1k
8	Undrewears3	November	16	2010	Area 2	Boy 6	1k
9	Undrewears4	December	5	2010	Area 3	Boy 7	1k
10	Undrewears5	June	5	2012	Area 2	Boy 8	1k
11	Sandals1	September	25	2010	Area 1	Boy 12	1k
12	Sandals2	October	25	2010	Area 2	Boy 10	1k
13	Sandals3	November	25	2010	Area 3	Boy 11	1k
14	Towels1	December	22	2010	Area 4	Boy 7	1k
15	Towels2	January	25	2010	Area 5	Boy 3	1k
16	Towels3	June	5	2010	Area 2	Boy 1	1k
17	Towels4	May	7	2011	Area 1	Boy 13	1k
18	Towels5	June	5	2010	Area 2	Boy 6	1k
19	Towels6	August	21	2010	Area 2	Boy 10	1k
20	Towels7	June	9	2011	Area 4	Boy 2	1k
21	Towels7	July	14	2011	Area 5	Boy 1	1k
22	Shorts1	August	9	2011	Area 1	Boy 9	1k
23	Shorts2	June	14	2011	Area 5	Boy 9	1k
24	Shorts2	June	14	2011	Area 1	Boy 4	1k
25	*	*	*	*	*	*	*
26	*	*	*	*	*	*	*
27	*	*	*	*	*	*	*
Masterlist

then this is my problem what formula to use on Cell A to M particularly starting from rows 3 downward. The scenario is this if i pick a year on cell D1(list down box/cell of years), the count of delivery in respective delivery boy and month will appear. the delivery boy is a name of a true person.

this is the sample sheet.
in year 2011

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Delivered by	Select a year		2011		Summary of Delivery per Delivery Boy
2		January	February	March	April	May	June	July	August	September	October	November	December
3	Boy 13	0	0	0	0	1	0	0	0	0	0	0	0
4	Boy 2	0	0	0	0	0	1	0	0	0	0	0	0
5	Boy 1	0	0	0	0	0	0	1	0	0	0	0	0
6	Boy 9	0	0	0	0	0	1	0	1	0	0	0	0
7	Boy 4	0	0	0	0	0	1	0	0	0	0	0	0
8	*	0	0	0	0	0	0	0	0	0	0	0	0
9	*	0	0	0	0	0	0	0	0	0	0	0	0
10	*	0	0	0	0	0	0	0	0	0	0	0	0
11	*	0	0	0	0	0	0	0	0	0	0	0	0
12	*	0	0	0	0	0	0	0	0	0	0	0	0
13	*	0	0	0	0	0	0	0	0	0	0	0	0
14	*	0	0	0	0	0	0	0	0	0	0	0	0
15	*	0	0	0	0	0	0	0	0	0	0	0	0
16	*	0	0	0	0	0	0	0	0	0	0	0	0
17	*	0	0	0	0	0	0	0	0	0	0	0	0
18	*	0	0	0	0	0	0	0	0	0	0	0	0
19	*	0	0	0	0	0	0	0	0	0	0	0	0
20	*	0	0	0	0	0	0	0	0	0	0	0	0
21	*	0	0	0	0	0	0	0	0	0	0	0	0
22	*	0	0	0	0	0	0	0	0	0	0	0	0
23	*	0	0	0	0	0	0	0	0	0	0	0	0
24	*	0	0	0	0	0	0	0	0	0	0	0	0
25	*	0	0	0	0	0	0	0	0	0	0	0	0
Delivery Count per Delivery Boy

this is the sample sheet.
in year 2011

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Delivered by	Select a year		2012		Summary of Delivery per Delivery Boy
2		January	February	March	April	May	June	July	August	September	October	November	December
3	Boy 8	0	0	0	0	0	1	0	0	0	0	0	0
4	*	0	0	0	0	0	0	0	0	0	0	0	0
5	*	0	0	0	0	0	0	0	0	0	0	0	0
6	*	0	0	0	0	0	0	0	0	0	0	0	0
7	*	0	0	0	0	0	0	0	0	0	0	0	0
8	*	0	0	0	0	0	0	0	0	0	0	0	0
9	*	0	0	0	0	0	0	0	0	0	0	0	0
10	*	0	0	0	0	0	0	0	0	0	0	0	0
11	*	0	0	0	0	0	0	0	0	0	0	0	0
12	*	0	0	0	0	0	0	0	0	0	0	0	0
13	*	0	0	0	0	0	0	0	0	0	0	0	0
14	*	0	0	0	0	0	0	0	0	0	0	0	0
15	*	0	0	0	0	0	0	0	0	0	0	0	0
16	*	0	0	0	0	0	0	0	0	0	0	0	0
17	*	0	0	0	0	0	0	0	0	0	0	0	0
18	*	0	0	0	0	0	0	0	0	0	0	0	0
19	*	0	0	0	0	0	0	0	0	0	0	0	0
20	*	0	0	0	0	0	0	0	0	0	0	0	0
21	*	0	0	0	0	0	0	0	0	0	0	0	0
22	*	0	0	0	0	0	0	0	0	0	0	0	0
23	*	0	0	0	0	0	0	0	0	0	0	0	0
24	*	0	0	0	0	0	0	0	0	0	0	0	0
25	*	0	0	0	0	0	0	0	0	0	0	0	0
Delivery per Delivery Boy

Please please help me!!!!!!!!!!!
Thank you in advance...

 

[venkat1926]

It is not made clear which is the third sheet. I have assumed the the third sheet in the order given as the third sheet.
this data is based on second sheet.
form this second sheet boy 13 delivered in May 2011, 7 items.
how can it be 1

I am sending you sheet 2 and sheet 3

Excel Workbook
	A	B	C	D	E	F	G
1	Products	Date Delivered	Location of Delivery	Delivered by	Sales
2	Month	Day	Year
3	Shoes1	June	8	2010	Area 1	Boy 7	1k
4	Shoes2	December	17	2010	Area 2	Boy 2	1k
5	Shoes3	August	7	2010	Area 3	Boy 3	1k
6	Undrewears1	September	5	2010	Area 5	Boy 22	1k
7	Undrewears2	October	5	2010	Area 5	Boy 5	1k
8	Undrewears3	November	16	2010	Area 2	Boy 6	1k
9	Undrewears4	December	5	2010	Area 3	Boy 7	1k
10	Undrewears5	June	5	2012	Area 2	Boy 8	1k
11	Sandals1	September	25	2010	Area 1	Boy 12	1k
12	Sandals2	October	25	2010	Area 2	Boy 10	1k
13	Sandals3	November	25	2010	Area 3	Boy 11	1k
14	Towels1	December	22	2010	Area 4	Boy 7	1k
15	Towels2	January	25	2010	Area 5	Boy 3	1k
16	Towels3	June	5	2010	Area 2	Boy 1	1k
17	Towels4	May	7	2011	Area 1	Boy 13	1k
18	Towels5	June	5	2010	Area 2	Boy 6	1k
19	Towels6	August	21	2010	Area 2	Boy 10	1k
20	Towels7	June	9	2011	Area 4	Boy 2	1k
21	Towels7	July	14	2011	Area 5	Boy 1	1k
22	Shorts1	August	9	2011	Area 1	Boy 9	1k
23	Shorts2	June	14	2011	Area 5	Boy 9	1k
24	Shorts2	June	14	2011	Area 1	Boy 4	1k
Sheet2

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Delivered by	Select a year	2011	Summary of Delivery per Delivery Boy
2	January	February	March	April	May	June	July	August	September	October	November	December
3	Boy 13	0	0	0	7	0	0	0	0	0	0	0	0
4	Boy 2	0	0	0	0	9	0	0	0	0	0	0	0
5	Boy 1	0	0	0	0	0	14	0	0	0	0	0	0
6	Boy 9	0	0	0	0	14	0	9	0	0	0	0	0
7	Boy 4	0	0	0	0	14	0	0	0	0	0	0	0
Sheet3

see the formula in sheet 3 in B3 which is copied down and right

 

[doms123]

the sheet 3 are 4 is my third sheet. the fourth is just another example of the third. thank you for the solution, but wheres the formula for the collumn A(the names).???? and i am not looking for the date when it delivers, i am looking for the count on how many the delivery boy delivers in its respective month.

 

[Haseeb Avarakkan]

Hello,

Firstly define these names, press Ctrl+F3, click New.

Name: Size
Refers to; =MATCH(REPT("Z",250),Masterlist!$B:$B)
click OK. click New.

Name: DeliMonth
Refers to: =Masterlist!$B$3:INDEX(Masterlist!$B:$B,Size)

Name: DeliPerson
Refers to: =Masterlist!$F$3:INDEX(Masterlist!$F:$F,Size)

Name: DeliYear
Refers to: =Masterlist!$D$3:INDEX(Masterlist!$D:$D,Size)

Go to the sheet 'Delivery Count per Delivery Boy'

A3, copy down. This is an Array Formula. Must hit CONTROL+SHIFT+ENTER, not just ENTER.

=IFERROR(INDEX(DeliPerson,SMALL(IF(FREQUENCY(IF(DeliYear=$D$1,
MATCH(DeliPerson,DeliPerson,0)),ROW(DeliPerson)-ROW(INDEX(DeliPerson,1,1))+1),
ROW(DeliPerson)-ROW(INDEX(DeliPerson,1,1))+1),ROWS(A$3:A3))),"")

B3, copy across & down, with just ENTER.

=COUNTIFS(DeliMonth,B$2,DeliYear,$D$1,DeliPerson,$A3)

 

[doms123]

can you explain those define names... and what it does