I need help on count formula....

[doms123]

Hi I'm using Excel 2007 and I'm having a problem on how to formulate count formula, I have 2 sheets first is the master list and the other is the summary list that is connected on the Masterlist.

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	Shorts3	July	14	2011	Area 2	Boy 4	1k
26	*	*	*	*	*	*	*
27	*	*	*	*	*	*	*
28	*	*	*	*	*	*	*
29	*	*	*	*	*	*	*
30	*	*	*	*	*	*	*
31	*	*	*	*	*	*	*
32	*	*	*	*	*	*	*
33	*	*	*	*	*	*	*
34	*	*	*	*	*	*	*
35	*	*	*	*	*	*	*
36	*	*	*	*	*	*	*
37	*	*	*	*	*	*	*
Masterlist Sheet

and this is my summary sheet per area and i need the formula on B3 up to M7

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

Please Help me!!!
Thank you in advance....

 

[CharlesChuckieCharles]

thats
=COUNTIFS(MasterList!E:E, $A3, MasterList!B:B, B$2)

=COUNTIFS(MasterList!$E:$E, $A3, MasterList!$B:$B, B$2, MasterList!$D:$D, $D$1)

 

[markmzz]

But it doesn't affect the yeear. The Cell D1 is a List Down cell of year, if i choose a year then it will count the delivery on its respective month,

Doms123,

Did you see this:

Doms123,

Try this:

<TABLE style="WIDTH: 750pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=1003><COLGROUP><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2560" width=70><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2889" span=11 width=79><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: #d8d8d8; WIDTH: 53pt; HEIGHT: 30pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl64 height=40 rowSpan=2 width=70>Delivered by</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; WIDTH: 118pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl65 width=158 colSpan=2>Select a year</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; WIDTH: 118pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl65 width=158 colSpan=2>2010</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; WIDTH: 461pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl65 width=617 colSpan=8>Summary per Area</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl70 height=20>January</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl70>February</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl70>March</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl70>April</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl70>May</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl70>June</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl70>July</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl70>August</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl70>September</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl70>October</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl70>November</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" class=xl71>December</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: #d8d8d8; WIDTH: 53pt; HEIGHT: 15.75pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl72 height=21 width=70>Area 1</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; WIDTH: 59pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl73 width=79 align=right>0</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; WIDTH: 59pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl73 width=79 align=right>0</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; WIDTH: 59pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl73 width=79 align=right>0</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; WIDTH: 59pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl73 width=79 align=right>0</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; WIDTH: 59pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl73 width=79 align=right>0</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; WIDTH: 59pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl73 width=79 align=right>1</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; WIDTH: 59pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl73 width=79 align=right>0</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; WIDTH: 59pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl73 width=79 align=right>0</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; WIDTH: 59pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl73 width=79 align=right>1</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; WIDTH: 59pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl73 width=79 align=right>0</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; WIDTH: 59pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl73 width=79 align=right>0</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl73 width=64 align=right>0</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: #d8d8d8; WIDTH: 53pt; HEIGHT: 15.75pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl72 height=21 width=70>Area 2</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; WIDTH: 59pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl73 width=79 align=right>0</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; WIDTH: 59pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl73 width=79 align=right>0</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; WIDTH: 59pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl73 width=79 align=right>0</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; WIDTH: 59pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl73 width=79 align=right>0</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; WIDTH: 59pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl73 width=79 align=right>0</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; WIDTH: 59pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl73 width=79 align=right>2</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; WIDTH: 59pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl73 width=79 align=right>0</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; WIDTH: 59pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl73 width=79 align=right>1</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; WIDTH: 59pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl73 width=79 align=right>0</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; WIDTH: 59pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl73 width=79 align=right>1</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; WIDTH: 59pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl73 width=79 align=right>1</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl73 width=64 align=right>1</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: #d8d8d8; WIDTH: 53pt; HEIGHT: 15.75pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl72 height=21 width=70>Area 3</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; WIDTH: 59pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl73 width=79 align=right>0</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; WIDTH: 59pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl73 width=79 align=right>0</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; WIDTH: 59pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl73 width=79 align=right>0</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; WIDTH: 59pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl73 width=79 align=right>0</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; WIDTH: 59pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl73 width=79 align=right>0</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; WIDTH: 59pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl73 width=79 align=right>0</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; WIDTH: 59pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl73 width=79 align=right>0</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; WIDTH: 59pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl73 width=79 align=right>1</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; WIDTH: 59pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl73 width=79 align=right>0</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; WIDTH: 59pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl73 width=79 align=right>0</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; WIDTH: 59pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl73 width=79 align=right>1</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl73 width=64 align=right>1</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: #d8d8d8; WIDTH: 53pt; HEIGHT: 15.75pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl72 height=21 width=70>Area 4</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; WIDTH: 59pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl73 width=79 align=right>0</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; WIDTH: 59pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl73 width=79 align=right>0</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; WIDTH: 59pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl73 width=79 align=right>0</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; WIDTH: 59pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl73 width=79 align=right>0</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; WIDTH: 59pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl73 width=79 align=right>0</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; WIDTH: 59pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl73 width=79 align=right>0</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; WIDTH: 59pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl73 width=79 align=right>0</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; WIDTH: 59pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl73 width=79 align=right>0</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; WIDTH: 59pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl73 width=79 align=right>0</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; WIDTH: 59pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl73 width=79 align=right>0</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; WIDTH: 59pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl73 width=79 align=right>0</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl73 width=64 align=right>1</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: #d8d8d8; WIDTH: 53pt; HEIGHT: 16.5pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl74 height=22 width=70>Area 5</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; WIDTH: 59pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl73 width=79 align=right>1</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; WIDTH: 59pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl73 width=79 align=right>0</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; WIDTH: 59pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl73 width=79 align=right>0</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; WIDTH: 59pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl73 width=79 align=right>0</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; WIDTH: 59pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl73 width=79 align=right>0</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; WIDTH: 59pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl73 width=79 align=right>0</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; WIDTH: 59pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl73 width=79 align=right>0</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; WIDTH: 59pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl73 width=79 align=right>0</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; WIDTH: 59pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl73 width=79 align=right>1</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; WIDTH: 59pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl73 width=79 align=right>1</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; WIDTH: 59pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl73 width=79 align=right>0</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #d8d8d8; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl73 width=64 align=right>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; mso-ignore: colspan" class=xl63 colSpan=8>=COUNTIFS(Masterlist!$D$3:$D$25;$D$1;Masterlist!$E$3:$E$25;$A7;Masterlist!$B$3:$B$25;B$2)</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; mso-ignore: colspan" class=xl63 colSpan=3>YEAR-DELIVERED BY-MONTH</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR></TBODY></TABLE>
Markmzz

Markmzz

 

[azo]

Doms123,

Did you see this:




Markmzz

Mark...I'm asking because this is a learning experience for me too...does your formula stop checking at row 25? What is the advantage to selecting specific cells verses the whole column?

Azo

 

[T. Valko]

But it doesn't affect the yeear. The Cell D1 is a List Down cell of year, if i choose a year then it will count the delivery on its respective month,

Ok, if you want to include the year as a condition then try this version:

=COUNTIFS(MasterList!$B$3:$B$25,B$2,MasterList!$D$3:$D$25,$D$1,MasterList!$E$3:$E$25,$A3)

 

[CharlesChuckieCharles]

Ok, if you want to include the year as a condition then try this version:

=COUNTIFS(MasterList!$B$3:$B$25,B$2,MasterList!$D$3:$D$25,$D$1,MasterList!$E$3:$E$25,$A3)

<b>=COUNTIFS(MasterList!$E:$E, $A3, MasterList!$B:$B, B$2, MasterList!$D:$D, $D$1)</b>
This works just as well

There is NO requirement to specifically order the Criteria

or chain your lookup to the first 25 rows

incase your dataset is variable

(althought if you actually have "*" 's in the unused cells clear them out!)

 

[markmzz]

Mark...I'm asking because this is a learning experience for me too...does your formula stop checking at row 25? What is the advantage to selecting specific cells verses the whole column?

Azo

Azo,

It's not a question of advantage.

I used to line 25 in function of the data presented, but nothing prevents you from improving the user's region of interest.

As this formula:

=COUNTIFS(Masterlist!$D:$D;$D$1;Masterlist!$E:$E;$A7;Masterlist!$B:$B;B$2)

In addition, it could also be used dynamic named ranges (with OFFSET).

Markmzz