have column of years (numeric), would like tocount how many by decade.

[mr_techie]

Hi,

I am new to VBA, an thus may be a trivial question, but I haven;t founf an answer, yet.

I have a column of years, 1921 to 2011, and would like to find out hoe many are in each decade, ie >=1920 and <=1929, for each decade.

the column is in numeric form, but could be easily converted to text.

any help would be appreciated.

thanks in advance!!

 

[AlphaFrog]

Hi and welcome.

Try something like this...

[color=darkblue]Dim[/color] count [color=darkblue]As[/color] [color=darkblue]Long[/color], decade [color=darkblue]As[/color] [color=darkblue]Long[/color]

[color=darkblue]For[/color] decade = 1920 [color=darkblue]To[/color] 2019 [color=darkblue]Step[/color] 10

    count = WorksheetFunction.CountIf(Range("A:A"), ">=" & decade) - WorksheetFunction.CountIf(Range("A:A"), ">" & decade + 9)
    MsgBox count, , "Count for " & decade & "s"
    
[color=darkblue]Next[/color] decade

 

[mr_techie]

Thanks!

Haven't tried it yet, but looks feasible.

don't know if I was clear on this detail, but column is in random order, not numeric.

 

[mr_techie]

the previous offfering doesn't work, with either text or numbers in the column.

the column has a year, as text, in each row, from 1929 to 2011. I want to count hw many are in each decade, >=1929 and <=1929, for each decade.

thought I could use c--COUNTIF, but it's not working.

here;s a sample of the column

<table border="0" cellpadding="0" cellspacing="0" width="35"><colgroup><col style="mso-width-source:userset;mso-width-alt:1280;width:26pt" width="35"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;width:26pt" height="20" width="35">1987</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">1968</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">1984</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">2007</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">2006</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">1982</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">1968</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">2001</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">1988</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">1981</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">1993</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">1991</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">2004</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">2001</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">1979</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">1979</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">1979</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">1979</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">1979</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">2010</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">2010</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">1973</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">2004</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">2005</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">1947</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">2003</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">1978</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">1980</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">1979</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">1999</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">1996</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">2011</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">2009</td> </tr> </tbody></table>

 

[mr_techie]

wound up doing 'brute-force- method -- tested for eaxh tear, then did sum for each 10 years. it works anyway

 

[Jonmo1]

this really shouldn't be that difficult...

Excel Workbook
	A	B	C
1	1987	1920	0
2	1968	1930	0
3	1984	1940	1
4	2007	1950	0
5	2006	1960	2
6	1982	1970	8
7	1968	1980	6
8	2001	1990	4
9	1988	2000	9
10	1981	2010	3
11	1993	2020
12	1991
13	2004
14	2001
15	1979
16	1979
17	1979
18	1979
19	1979
20	2010
21	2010
22	1973
23	2004
24	2005
25	1947
26	2003
27	1978
28	1980
29	1979
30	1999
31	1996
32	2011
33	2009
Sheet1

 

[mr_techie]

thanks! interesting ...
now I need that in VBA - have about 700 records to search.

on a similar note, have a column od media types - DVD, DVD(5), DVD(50) - and was trying to copy matching rows to a sheet. DVD works, but can't seem to be able to find the other two cases. I am actually looking at DVD(?) and DVD(??). I have tried IF THEN and CASE SELECT, but while they find DVD, neither finds the other cases.

 

[Jonmo1]

If My formula works, then AlphaFrog's VBA code will work as well.

 

[kreases]

No one has said why the countif function isn't working on a column of years, I am trying to do the same thing count the number of years that fall in a decade, have tried using a wildcard but just get zero back. COUNTIF(SINGLES!Q3:Q601,"196?") I have also tried * instead of the ? but still returns a zero. I have tried formatting the column on years as numeric, text and general and all return zero.

Any ideas?

 

[Tetra201]

Try

=COUNTIFS(SINGLES!Q3:Q601,">=1960",SINGLES!Q3:Q601,"<1970")