countif problem

[Legacy 169354]

Hi,

I have a simple task that needs to count the rows that fit the criteria below:

1) count the dates that equal month AND year of the date column and then places that count in the column of the first date change.


<table border="0" cellpadding="0" cellspacing="0" width="128"><col style="width: 48pt;" span="2" width="64"> <tbody><tr style="height: 15.75pt;" height="21"> <td class="xl65" style="height: 15.75pt; width: 48pt;" height="21" width="64">date
</td> <td class="xl65" style="width: 48pt;" width="64"> total</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" height="20">Jan-07</td> <td class="xl65"> 3</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" height="20">Jan-07</td> <td class="xl65">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" height="20">Jan-07</td> <td class="xl65">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" height="20">Feb-07</td> <td class="xl65"> 4</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" height="20">Feb-07</td> <td class="xl65">
</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl66" style="height: 15.75pt;" height="21">Feb-07</td> <td class="xl65">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" height="20">Feb-07</td> <td class="xl65">
</td> </tr> </tbody></table>

for some reason count if aint working for me.
There are about 1000 rows of dates I need a count for.
Thanks!
PS - Excel 2003

 

[Legacy 169354]

Hi,

Excel 2007<table style="background-color: rgb(255, 255, 255); border: 1px solid rgb(166, 170, 182); border-collapse: collapse;" cellpadding="2.5px" rules="all"><colgroup><col style="background-color: rgb(224, 224, 240);" width="25px"><col><col><col><col><col><col></colgroup><thead><tr style="background-color: rgb(224, 224, 240); text-align: center; color: rgb(22, 17, 32);"><th>
</th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr><td style="color: rgb(22, 17, 32); text-align: center;">1</td><td style="">date</td><td style="">total</td><td style="text-align: right;">
</td><td style="">Enter year</td><td style="text-align: right;">2007</td><td style="text-align: right;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">2</td><td style="text-align: right;">Jan-07</td><td style="text-align: right;">3</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: rgb(22, 17, 32); text-align: center;">3</td><td style="text-align: right;">Jan-07</td><td style="">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">Jan-07</td><td style="text-align: right;">Feb-07</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">4</td><td style="text-align: right;">Jan-07</td><td style="">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">3</td><td style="text-align: right;">4</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">5</td><td style="text-align: right;">Feb-07</td><td style="text-align: right;">4</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: rgb(22, 17, 32); text-align: center;">6</td><td style="text-align: right;">Feb-07</td><td style="">
</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: rgb(22, 17, 32); text-align: center;">7</td><td style="text-align: right;">Feb-07</td><td style="">
</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: rgb(22, 17, 32); text-align: center;">8</td><td style="text-align: right;">Feb-07</td><td style="">
</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></tbody></table>

Sheet8​

<table style="border: 2px solid black; border-collapse: collapse; padding: 0.4em; background-color: rgb(255, 255, 255);" cellpadding="2.5px" rules="all" width="85%"><tbody><tr><td style="padding: 6px;">Worksheet Formulas<table style="border: 1px solid rgb(166, 170, 182); text-align: center; background-color: rgb(255, 255, 255); border-collapse: collapse;" cellpadding="2.5px" rules="all" width="100%"><thead><tr style="background-color: rgb(224, 224, 240); color: rgb(22, 17, 32);"><th width="10px">Cell</th><th style="text-align: left; padding-left: 5px;">Formula</th></tr></thead><tbody><tr><th style="background-color: rgb(224, 224, 240); color: rgb(22, 17, 32);" width="10px">B2</th><td style="text-align: left;">=IF(TEXT(A1,"mmyyyy")<>TEXT(A2,"mmyyyy"),COUNTIF(A:A,">="&A2-DAY(A2)+1)-COUNTIF(A:A,">"&EOMONTH(A2,0)),"")</td></tr></tbody></table></td></tr></tbody></table>

Do you need the count in the 2nd col? As Jeff suggested a Pivot Table can give you the whole month count. also formulas.

Is it Ok shown in the D1:E2 for the count?

it seems to be tallying the total of the columnA.

 

[Haseeb Avarakkan]

If you dates are in ascending order try this,

H366, copy down & up

=IF(TEXT(A366,"mmyy")<>TEXT(A365,"mmyy"),COUNT(A366:INDEX(A366:$A$1000,MATCH(EOMONTH(A366,0),A366:$A$1000,1))),"")

 

[Legacy 169354]

Thanks for the effort Haseeb,

the result seems to make a 1 and then a zero underneath it.

sorry to be a pain.

thanks