counting values

[Dean]

I have a list of building data. Within that data, there are 15 unique
towns. I want to know how many times a building of a certain age group
occurs in each town. There are 13 age groups. For example:

Los Angeles - 1930 = 34,000
Santa Barbara - 1910 = 670

I have tried COUNTIF and other formulas, as well as advanced
filtering, and I have not been able to find the right thing to help
me. Can anyone offer any assistance?

Thanks in advance,
Dean

 

[Bernard Liengme]

Are the city names in one column and the years in another? If so
=SUMPRODUCT(--(a1:A3000="Los Angeles),--(B1:B3000=1930))

or read about pivot tables

http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
http://www.ozgrid.com/Excel/excel-pivot-tables.htm
http://www.techonthenet.com/excel/pivottbls/index.htm
http://www.dicks-blog.com/archives/2005/06/23/download-pivottable-parameters/

best wishes

 

[Dean]

Hi Bernard,

Thanks for the reply but it doesn't seem to give me the answer I was
looking for. Let me explain further...

Here is a sample of my data:

colA colB
Taneatua 1870
Ohope 1870
Matata 1870
Ohope 1890
Ohope 1890
Taneatua 1900
Taneatua 1900
Matata 1900

I have over 37,000 records, which have 15 unique town names and 13
unique age categories. What I am trying to do is get the number of age
categories per town; i.e. in the sample above I would end up with a
table such as this:

1870 1890 1900
Taneatua 1 0 1
Ohope 1 2
Matata 1 0 1

I am trying to use SUMPRODUCT but as of yet, no luck. Any further
assistance would be helpful.

Regards,
Dean

 

[Bernard Liengme]

The formula I gave you should work if there are in fact A entries for Los
Angles with corresponding B values of 1930. You may send me (not the
newsgroup) a file if you wish.

You could also try a pivot table
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
http://www.ozgrid.com/Excel/excel-pivot-tables.htm
http://www.techonthenet.com/excel/pivottbls/index.htm
http://www.dicks-blog.com/archives/2005/06/23/download-pivottable-parameters/

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

Hi Bernard,

Thanks for the reply but it doesn't seem to give me the answer I was
looking for. Let me explain further...

Here is a sample of my data:

colA colB
Taneatua 1870
Ohope 1870
Matata 1870
Ohope 1890
Ohope 1890
Taneatua 1900
Taneatua 1900
Matata 1900

I have over 37,000 records, which have 15 unique town names and 13
unique age categories. What I am trying to do is get the number of age
categories per town; i.e. in the sample above I would end up with a
table such as this:

1870 1890 1900
Taneatua 1 0 1
Ohope 1 2
Matata 1 0 1

I am trying to use SUMPRODUCT but as of yet, no luck. Any further
assistance would be helpful.

Regards,
Dean

 

[Dean]

Thanks Bernard,

Of course you were right, I was not paying attention to my syntax and
had it wrong. I guess I had been looking at too many formulas! Thanks
for your help.

Cheers,
Dean