Date Counting

[Joneye]

ive made a few attempts to count dates. The requirements are below

Count the dates in cells B3 to B7 and drop the answers accordingly into E9 to P9, the problems are understanding how many days are in a month and what year

as a visual i put in the desired answers in..

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Prodcut</td><td style=";">MOT Date</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">Jan</td><td style=";">Feb</td><td style="text-align: center;;">Mar</td><td style=";">Apr</td><td style="text-align: center;;">May</td><td style=";">Jun</td><td style="text-align: center;;">Jul</td><td style=";">Aug</td><td style="text-align: center;;">Sep</td><td style=";">Oct</td><td style="text-align: center;;">Nov</td><td style=";">Dec</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Car</td><td style="text-align: right;;">01/01/2011</td><td style="text-align: right;;"></td><td style=";">attemepts</td><td style="text-align: center;;">0</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Van</td><td style="text-align: right;;">02/02/2011</td><td style="text-align: right;;"></td><td style=";">attemepts</td><td style="text-align: center;;">1</td><td style="text-align: center;;">0</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Van</td><td style="text-align: right;;">02/02/2011</td><td style="text-align: right;;"></td><td style=";">attemepts</td><td style="text-align: center;;"></td><td style="text-align: center;;">0</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Bus</td><td style="text-align: right;;">03/03/2011</td><td style="text-align: right;;"></td><td style=";">attemepts</td><td style="text-align: center;;">0</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">Bike</td><td style="text-align: right;;">04/04/2011</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Sort After Answer</td><td style="font-weight: bold;text-align: center;;">1</td><td style="font-weight: bold;text-align: center;;">2</td><td style="font-weight: bold;text-align: center;;">1</td><td style="font-weight: bold;text-align: center;;">4</td><td style="font-weight: bold;text-align: center;;"></td><td style="font-weight: bold;text-align: center;;"></td><td style="font-weight: bold;text-align: center;;"></td><td style="font-weight: bold;text-align: center;;"></td><td style="font-weight: bold;text-align: center;;"></td><td style="font-weight: bold;text-align: center;;"></td><td style="font-weight: bold;text-align: center;;"></td><td style="font-weight: bold;text-align: center;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E3</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B$3:$B$7,E2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E4</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B$3:$B$7,"1/1/2011"</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F4</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B$3:$B$7,F2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F5</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B$3:$B$7,AND(<font color="Red">$B$3:$B$7>="01/01/2011",$B$3:$B$7<="30/01/2011"</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E6</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">$B$3:$B$7=E1</font>)</td></tr></tbody></table></td></tr></table><br />

 

[Peter_SSs]

Why does cell H9 contain a 4? Should it be a 1?.

Do you want answers in row 9 or should there be something in each of row 3, 4, 5 etc.
Show your sample sheet again, exactly how you would want it to finish up for this data.

 

[MikeWx]

I'm not sure how you get 4 under April, but based on the January through March values, you can do the following:

1. Change the values in E2 through P2 to 1/1/2011, 2/1/2011, etc; and format them as "mmm"

2. Enter the following formula in E3 and copy it across to P3:

=SUMPRODUCT(MONTH($B$3:$B$7=MONTH(E$2),YEAR($B$3:$B$7=YEAR(E$2))

 

[Joneye]

Why does cell H9 contain a 4? Should it be a 1?.

Do you want answers in row 9 or should there be something in each of row 3, 4, 5 etc.
Show your sample sheet again, exactly how you would want it to finish up for this data.

Well spotted H9 should be 1

 

[Peter_SSs]

I'm not sure how you get 4 under April, but based on the January through March values, you can do the following:

1. Change the values in E2 through P2 to 1/1/2011, 2/1/2011, etc; and format them as "mmm"

2. Enter the following formula in E3 and copy it across to P3:

=SUMPRODUCT(MONTH($B$3:$B$7=MONTH(E$2),YEAR($B$3:$B$7=YEAR(E$2))

Did you test that formula? Seems to have some syntax errors to me.

 

[Joneye]

Peter,

It fails for me also, but I feel its close to where id like to be, now we have H9 accounted for as a 1

 

[Peter_SSs]

Well spotted H9 should be 1

What about the second part of my post?

Do you want answers in row 9 or should there be something in each of row 3, 4, 5 etc.
Show your sample sheet again, exactly how you would want it to finish up for this data.

 

[Joneye]

The answers are to go from E9 to P9 providing the dates are showing.

So im still looking for a forumla to count the range in B3 to B7 then for it to appear counted accordlingly in E9 - P9.

 

[Peter_SSs]

Try this, copied across.

Excel Workbook
	C	D	E	F	G	H	I	J
2			Jan	Feb	Mar	Apr	May	Jun
3	1/01/11
4	2/02/11
5	2/02/11
6	3/03/11
7	4/04/11
8
9			1	2	1	1	0	0
Dates

 

[Joneye]

Peter,

Appologies for the late reply, using excel 2003, am i missing a trick here my results are as below.

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">2011</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Prodcut</td><td style=";">MOT Date</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">Jan</td><td style="text-align: right;;">Feb</td><td style="text-align: center;;">Mar</td><td style="text-align: right;;">Apr</td><td style="text-align: center;;">May</td><td style="text-align: right;;">Jun</td><td style="text-align: center;;">Jul</td><td style="text-align: right;;">Aug</td><td style="text-align: center;;">Sep</td><td style="text-align: right;;">Oct</td><td style="text-align: center;;">Nov</td><td style="text-align: right;;">Dec</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Car</td><td style="text-align: right;;">01/01/2011</td><td style="text-align: right;;"></td><td style=";">attemepts</td><td style="text-align: center;;">1</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Van</td><td style="text-align: right;;">02/02/2011</td><td style="text-align: right;;"></td><td style=";">attemepts</td><td style="text-align: center;;">1</td><td style="text-align: center;;">0</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Van</td><td style="text-align: right;;">03/02/2011</td><td style="text-align: right;;"></td><td style=";">attemepts</td><td style="text-align: center;;"></td><td style="text-align: center;;">0</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Bus</td><td style="text-align: right;;">03/03/2011</td><td style="text-align: right;;"></td><td style=";">attemepts</td><td style="text-align: center;;">0</td><td style="text-align: center;;">4</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">Bike</td><td style="text-align: right;;">04/04/2011</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Sort After Answer</td><td style="font-weight: bold;text-align: center;;">0</td><td style="font-weight: bold;text-align: center;;">0</td><td style="font-weight: bold;text-align: center;;">0</td><td style="font-weight: bold;text-align: center;;">0</td><td style="font-weight: bold;text-align: center;;">0</td><td style="font-weight: bold;text-align: center;;">0</td><td style="font-weight: bold;text-align: center;;">0</td><td style="font-weight: bold;text-align: center;;">0</td><td style="font-weight: bold;text-align: center;;">0</td><td style="font-weight: bold;text-align: center;;">0</td><td style="font-weight: bold;text-align: center;;">0</td><td style="font-weight: bold;text-align: center;;">0</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E3</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B$3:$B$7,E2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E4</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B$3:$B$7,"1/1/2011"</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F4</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B$3:$B$7,F2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F5</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B$3:$B$7,AND(<font color="Red">$B$3:$B$7>="01/01/2011",$B$3:$B$7<="30/01/2011"</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E6</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">$B$3:$B$7=E1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F6</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$B$3:$B$7>=F2</font>),--(<font color="Red">$B$3:$B$7>=F2</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E9</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">TEXT(<font color="Green">$B$3:$B$7,"mmm"</font>)=E$2</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F9</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">TEXT(<font color="Green">$B$3:$B$7,"mmm"</font>)=F$2</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G9</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">TEXT(<font color="Green">$B$3:$B$7,"mmm"</font>)=G$2</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H9</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">TEXT(<font color="Green">$B$3:$B$7,"mmm"</font>)=H$2</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">I9</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">TEXT(<font color="Green">$B$3:$B$7,"mmm"</font>)=I$2</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">J9</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">TEXT(<font color="Green">$B$3:$B$7,"mmm"</font>)=J$2</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">K9</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">TEXT(<font color="Green">$B$3:$B$7,"mmm"</font>)=K$2</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">L9</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">TEXT(<font color="Green">$B$3:$B$7,"mmm"</font>)=L$2</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">M9</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">TEXT(<font color="Green">$B$3:$B$7,"mmm"</font>)=M$2</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">N9</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">TEXT(<font color="Green">$B$3:$B$7,"mmm"</font>)=N$2</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">O9</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">TEXT(<font color="Green">$B$3:$B$7,"mmm"</font>)=O$2</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">P9</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">TEXT(<font color="Green">$B$3:$B$7,"mmm"</font>)=P$2</font>)</font>)</td></tr></tbody></table></td></tr></table><br />