#### counting values #2

```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?

Dean
```
 0
11/18/2008 2:21:53 AM
excel 39879 articles. 2 followers.

4 Replies
349 Views

Similar Articles

[PageSpeed] 58

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

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

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

>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?
>
> Dean

```
 0
bliengme5824 (3040)
11/18/2008 3:10:02 AM
```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

Regards,
Dean

On Nov 18, 4:10=A0pm, "Bernard Liengme" <blien...@stfx.TRUENORTH.ca>
wrote:
> Are the city names in one column and the years in another? If so
> =3DSUMPRODUCT(--(a1:A3000=3D"Los Angeles),--(B1:B3000=3D1930))
>
>
> http://www.cpearson.com/excel/pivots.htmhttp://peltiertech.com/Excel/Pivo=
ts/pivotstart.htmhttp://www.contextures.com/xlPivot02.htmlhttp://www.ozgrid=
..com/Excel/excel-pivot-tables.htmhttp://www.techonthenet.com/excel/pivottbl=
e-par...
>
> best wishes
> --
> Bernard V Liengme
> Microsoft Excel MVPhttp://people.stfx.ca/bliengme
> remove caps from email
>
> "Dean" <DeanPodol...@googlemail.com> wrote in message
>
>
> >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 =3D 34,000
> > Santa Barbara - 1910 =3D 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?
>
> > Dean

```
 0
11/18/2008 8:33:17 PM
```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

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

Regards,
Dean

On Nov 18, 4:10 pm, "Bernard Liengme" <blien...@stfx.TRUENORTH.ca>
wrote:
> Are the city names in one column and the years in another? If so
> =SUMPRODUCT(--(a1:A3000="Los Angeles),--(B1:B3000=1930))
>
>
>
> best wishes
> --
> Bernard V Liengme
> Microsoft Excel MVPhttp://people.stfx.ca/bliengme
> remove caps from email
>
> "Dean" <DeanPodol...@googlemail.com> wrote in message
>
>
> >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?
>
> > Dean

```
 0
bliengme5824 (3040)
11/18/2008 9:24:18 PM
```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

Cheers,
Dean

On Nov 19, 9:33=A0am, Dean <DeanPodol...@googlemail.com> wrote:
> 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 =A0 =A0 =A0 =A0 =A0colB
> Taneatua =A0 =A0 =A0 =A01870
> Ohope =A0 =A0 =A01870
> Matata =A0 =A0 =A01870
> Ohope =A0 1890
> Ohope =A0 1890
> Taneatua =A0 =A0 =A0 =A01900
> Taneatua =A0 =A0 =A0 =A01900
> Matata =A0 =A0 =A01900
>
> 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:
>
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A01870 =A01890 =A01900
> Taneatua =A0 =A0 =A0 1 =A0 =A0 =A0 =A00 =A0 =A0 =A01
> Ohope =A0 =A0 =A0 =A0 =A0 1 =A0 =A0 =A0 =A02
> Matata =A0 =A0 =A0 =A0 =A01 =A0 =A0 =A0 =A00 =A0 =A0 =A01
>
> I am trying to use SUMPRODUCT but as of yet, no luck. Any further
>
> Regards,
> Dean
>
> On Nov 18, 4:10=A0pm, "Bernard Liengme" <blien...@stfx.TRUENORTH.ca>
> wrote:
>
> > Are the city names in one column and the years in another? If so
> > =3DSUMPRODUCT(--(a1:A3000=3D"Los Angeles),--(B1:B3000=3D1930))
>
>
> >http://www.cpearson.com/excel/pivots.htmhttp://peltiertech.com/Excel/...=
....
>
> > best wishes
> > --
> > Bernard V Liengme
> > Microsoft Excel MVPhttp://people.stfx.ca/bliengme
> > remove caps from email
>
> > "Dean" <DeanPodol...@googlemail.com> wrote in message
>
..
>
> > >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 grou=
p
> > > occurs in each town. There are 13 age groups. For example:
>
> > > Los Angeles - 1930 =3D 34,000
> > > Santa Barbara - 1910 =3D 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

```
 0
11/18/2008 9:58:29 PM

Similar Artilces:

Pivot table and counting.
I have a pivot table that gives me the number of rides done by a van. I can have the pivot table return the number of rides, but what I need is the number vans that participated for a certain day. Example, it is possible to have 27 trips done by 18 vans, how can I count the number of vans and not addup the van number? Any help appreciated. Thank you, Joe Hi Joe We need a bit more information on how your source data table is set up. What information do you have in each column? -- Regards Roger Govier sacredarms <sacredarms@discussions.microsoft.com> wrote: > I have a pi...

Using form to enter "query criteria" (between values)
Good morning everyone, I want to create a form with two boxes, where first box is lower limit of the value and the second upper limit. This values should be used in my query as filter criteria. Like this (query filter criteria): > "textbox1" AND < "textbox2" Does anyone know how I can build this in a form (i.e. connect my textboxes and query criteria)? Kindly, Mikael Sweden By thinking about for another minute I found the (simple) answer. In the query criteria I put the following expression: BETWEEN [forms].[nameofform].[NameofDatefield1] AND [forms].[name...

1. Selecting field with "enter"; 2. permanently accepting macros
I have recently upgraded to Access 2007 and two characteristics of my former database are not coming through & I want to get them back. First, in Access 2003, I had set it up so that if I hit "enter" the entire field would be selected. Now "enter" causes the cursor to jump to the next field. How can I change this? Secondly, In my database forms, I had incorporated buttons. Now, each time I open the database, I am given a warning about macros and I have to manually tell the program that the macros are trusted. Unfortunately, Access doesn't accept this an...

comparing data #2
I was wondering is there a way to compare data from 2 columns in a side by side comparison. I have a large list of numbers and what I want to do is copy another list of numbers into the spreadsheet. But I want the numbers in cell A1 and B1 to be equal if they are is not a match then leave cell b1 empty. I want that to continue all the way through the entire worksheet. Maurice, Do you want to end up with 2 separate columns, or are you wanting to merge the 2 lists into 1 list, without duplications? Dave I want 2 side by side columns. With the numbers that are equal right next to each othe...

Cycle Counting
Hello, I am looking for ideas on how to cycle count serial number tracked items without increasing workload. Our serialized items are barcoded but the problem is the are situated on the pallet incorrectly and the pallet is placed on a third level of a racking system. If we could just cycle count based on item count and not include the serial number it would make it easy. Is this possible in Great Plains. We are running Great Plains 8.0. Any suggestions are greatly aprreciated. Thank You, Brian Morris Database Administrator ...

Count
Hi, I would like to know how which formula I should use to count some codes & to ignore the duplicated codes, for example in column A: Code 1234 4321 4321 1234 1234 3214 Now, I want Excel to count & the result is 3 which are: 1234-4321-3214 Yes I can use PivotTable, but I would like to use a formula because I have to deal with this every day. I appreciate your support. -- MFS22 COUNTIF will do it. =COUNTIF(Rng,Rng), where the criterion is the same as the range: e.g =COUNTIF(A3:A8,A3:A8) HTH Peter "MFS" wrote: > Hi, > I wo...

2 2k7 servers, no owa?
i have 2 e2k7 servers on a test envirnment with mailbox, hub and client access roles on two domain controllers(both global catalog). i'm testing recovery procedures so i shutdown server1, after that i can't access owa on server2 and all exchange services crashed, after starting them, owa times out and load nothing. any ideas? ...

To keep in just one cell all values related to a monthly rate
I have to update in a monthly basis a currency rate. I know that there is a function, formula (or something like that) to link a month descripton to a number. e.g. September09 1,325 October09 2,235 November09 1,258 I would like to select correspondent month and the cell be updated with the value. Data Validation dropdown list for choosing the month combined with a lookup table and VLOOKUP function. For help on creating data validation dropdown lists see Debra Dalgleish's site. http://www.contextures.on.ca/xlDataVal01.html For help on VLOOKUP to populat...

How do I stop a user with no admin rights from reading other email #2
How do I stop a user with no possible admin rights from reading other emails. How is this possible. ...

Can I limit a cell to 2 characters?
Is there a way in Excel to limit the number of characters in a cell? Thanks Frank By checking the length of the cell =LEN(A1)=2 in your custom validation. Look for Data Validation on Debra Dalgleish's site. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Frankc" <Frankc@discussions.microsoft.com> wrote in message news:842747F1-1792-4BB5-B3CE-707C7CC5F88F@microsoft.com... > Is there a way in Excel to lim...

How do I count the # of times a value reoccurs and plot it over ti
I wish to plot a number of errors made by each users and compare it over time. I have a column of user ID's and a column of dates. I want the chart to count the number of times each user ID appears and plot against it's corresponding date. However, each time I try to do this, the chart appears wrong. Any ideas? In article <9595B2A1-8282-4AB3-8511-A6776E108164@microsoft.com>, Stephen@discussions.microsoft.com says... > I wish to plot a number of errors made by each users and compare it over > time. I have a column of user ID's and a column of dates. I want t...

Converting from Quicken #2
I have just purchased Money Standard and want to convert my quicken data, but it keeps telling me that it can't find all the Quicken files. I have searched teh whole HD for files with the same name as my data file and copied them to the same dir but still no luck. When I download the demo from the web it converts it with no hassles but then I can't get the satandard version to open that file. Any help would be appreciated as I don't want to loose years of work. Thanx In microsoft.public.money, MCLuke wrote: >I have just purchased Money Standard and want to convert my...

Incorrect value on the SMTP HELO/EHLO
I am looking for some help on this one. I inherited some exchange admin duties and to be honest it's not my specialty. We recently switched IP providers and our IP addresses changed. This also means we changed our DNS provider and I believe they have the appropriate zone records setup, although this may not be the case since I don't recall having this issue with our previous provider. When sending email to external email addresses we get NDR's from several different ISPs usually stating they are blocked because our IP address does not match the domain. The mailserver identifie...

error in compiling 2002 code in 2005 #2
Hi i have code build in visual studio 2002 . when i open that code in 2005 it pops up conversion wizard.. it convert successfully . but when i build it gives error is something is missing ? it gives error in "Iterator" keyword? p2 On 24 Apr 2007 01:36:16 -0700, p2 <pradip.patel@einfochips.com> wrote: >Hi > >i have code build in visual studio 2002 . > >when i open that code in 2005 it pops up conversion wizard.. > >it convert successfully . >but when i build it gives error > >is something is missing ? > >it gives error in "It...

How to view 2 Excel worksheets from different workbooks same time
I have two monitors. I would like to open and view 2 different worksheets from 2 different workbooks at the same time. Is this possible with Excel? I would open excel (a single instance). Then use File|open to open both workbooks. Then I'd look at Chip Pearson's notes: http://cpearson.com/excel/MultipleMonitors.aspx dotflower wrote: > > I have two monitors. I would like to open and view 2 different worksheets > from 2 different workbooks at the same time. Is this possible with Excel? -- Dave Peterson i have 3 monitors an i usually open 3 instances of e...

Sum Values depending on other values
I would like to have a formula in a single cell that will sum the values in range E:E if the corresponding value in G:G is >0. for example: Col G Col E 5 1 0 8 9 3 0 7 The sum of Col E is 4. Thanks, Bernie =SUMIF(G:G,">0",E:E) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "bw" <iamnu@cableone.net> wrote in message news:12060mofq6cef5c@corp.supernews.com... > I would like to have a formula in a single cell that will sum the values in > range E:E if the c...

Pre defined value in Text Box
I have input box that when certain criteria are met needs a predefined value in a text box within the input box. I have used the following code to try this but it doesn't seem to work Activity_Text.Text = "812402" Job_No_Text.Text = "" Rec_Point_Text.Text = "" Activity_Text, Job_No_Text and Rec_Point_Text are the named text boxes. When the input box opens the Activity text box is empty!!! Many thanks in advance ...

Can we install Rollup 2 for CRM 1.2 Build v1.2.3297.0
Hi, We have a pending upgrade from version 1.2 to v3.0 I was thinking of istalling the rollup for ver 1.2 before upgrading to version 3.0. The current build of the server is v1.2.3297.0 The link below suggests that the build #for rollup 2 1.02.3297.173 Has any body come up with issues when installing the rollup on the 1.2 server (build is 1.2.3297.0) http://support.microsoft.com/default.aspx?kbid=904435# Or is it better to try and directly upgrade to 3.0 rather than putting the roll up and then doing the upgrade? Suggestions Please.... If you are going to upgrade, then I would just ...

Newbie question / plug in a value for a cell based on a range for that value
Hey guys, New to Excel so sorry if this explanation isn't the clearest in terms of correct terminology. In one section of my spreadsheet I have a list of percentage values associated with numbers 1-20. For example #10 is listed with the percentage value of 38.4% in the cell next to it (O14 and P14). In another section of the spreadsheet I have a quick formula that I'd like to use these %'s based on what number (1-20) is entered. In my spreadsheet the % I want to use for this #10 is listed in P14 (38.4%). What can I plug into my formula so when I enter "10" is kno...

hi I am trying to read the values from a excel row. There are 89 columns for each row and same number of data in each rows. Kindly help regards shantanu shantanu, You need to explain how/where you want to read them.... From another workbook, another worksheet, a VBA function, a macro, a C# application? HTH, Bernie MS Excel MVP "shantanu" <shantanusenin@gmail.com> wrote in message news:1184298794.259390.216180@i38g2000prf.googlegroups.com... > hi > I am trying to read the values from a excel row. There are 89 > columns for each row and same number of da...

count number of characters in rich edit control
How can i count number of characters in rich edit control 2 If u mean controls of type Rich TextBox control, try RichControl1 RC1; CString Mes=RC.GetText(); int n=Mes.GetLength(); "Rahul" <Rahul@discussions.microsoft.com> wrote in message news:7743023C-EA83-4ACA-AB67-F1AE7279F1EB@microsoft.com... > How can i count number of characters in rich edit control 2 EM_GETTEXTLENGTHEX, WM_GETTEXTLENGTH, WM_GETTEXT, EM_STREAM* "Rahul" <Rahul@discussions.microsoft.com> wrote in message news:7743023C-EA83-4ACA-AB67-F1AE7279F1EB@microsoft.com... > How can i co...

Multiply all query by same value
...

getting rid of zero values in a column
i have a set of data on a column that i`ll use to plot on a graph and find a trendline for its behaviour...problem is: there is zero values in some cells that were not calculated... i want to copy this data in a new column getting rid of cells with zero value because it results in a wrong trendline...how can i do this?please help this is an example of what i mean: 1 1 3 3 4 4 0 6 6 3 3 1 0 0 0 1 One of the simpler ways is to hide the rows for the cells that have zeros and then plot the graph. "kafoury123" wrote: > i have a set of data on a ...

Copy #2
Hi... I have a small problem but still need some help. I have a workbook called "Sumary.xls" located in a folder Dim strPath As String Dim strParent As String Dim pos As Long Dim Slutt As String strPath = ActiveWorkbook.Path **** Then i have to open another workbook called "Statistikk.xls" pos = InStrRev(strPath, "\") strParent = Left(strPath, pos - 1) Slutt = strParent & "\VT" & "\VT" & "\Arkiv\" & "Statistikk.xls" Workbooks.Open Slutt ***Everything is ok with this, but: ***** Then i want to...

Track 1 and 2,Fullz,Skimmer,MSR
USA BANKLOGIN CANADA LOGINS UK LOGINS US CVV UK CVV EU CVV FULL CC with mmn,ssn,dob,pin = pm me for price US Dumps: Canada Dumps: Europe Dumps: Asia Dumps Atm Skimmer Wincor Nixdorf = 3000 Atm Skimmer Wincor = 3000 Atm Skimmer Slimm = 3000 Atm Skimmer Slim = 3000 Atm Skimmer NCR = 3000 Atm Skimmer Diebold Opteva = 2500 Atm Skimmer Diebold = 2000 Atm Skimmer Universal = 4000 Atm Skimmer Small = 2500 Chip POS ingenico&amigo = 1500 MSR206 MSE ETC PRINTED TRACKS ON PLASTIC WITH PIN FOR SALE skimmer-service.com YIM : ha_tuyen6@yahoo.com ICQ : 37630432...