counting values

  • Thread starter Thread starter Dean
  • Start date Start date
D

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
 
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
 
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
 
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
 
Back
Top