Results 1 to 14 of 14
  1. #1
    New Lounger
    Join Date
    Oct 2008
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Counting instances (Excel 2003)

    In the attached spreadsheet example I want to count how many times does "John=A" and "John = B" etc and count the occurrances or A, B, and C?
    Hope this makes sense?

    Regards
    B
    Attached Files Attached Files

  2. #2
    New Lounger
    Join Date
    Oct 2008
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting instances (Excel 2003)

    Sorry Hans - there is no attachment to your post.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Counting instances (Excel 2003)

    Edited to add attachment

    I'd choose a slightly different layout and create a pivot table - see the attached workbook.
    The pivot table isn't updated automatically; if the data have changed, click anywhere in the pivot table, then click the refresh button on the Pivot Table toolbar.
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Counting instances (Excel 2003)

    Oops, sorry about that. I must have hit "Post It" too soon. I have edited my previous reply and added the attachment.

  5. #5
    New Lounger
    Join Date
    Oct 2008
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting instances (Excel 2003)

    WOW! Thanks Hans. I have never made a Pivot Table but have heard they are an excellent tool. I can see I am going to have to investigate them further. Two questions.

    Will you tell me how you created this example - so that I can recreate it myself.

    Can you point me to some good instructions on Pivot Tables with some examples?

    Regards
    B

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Counting instances (Excel 2003)

    I first added "field names" (column headings) above the two columns of data; a pivot table expects the data to have field names.

    I then clicked in an arbitrary cell in the data, and selected Data | Pivot Table and Pivot Chart Report...
    The option "A Microsoft Excel table or database" was OK, so I clicked Next.
    Excel automatically selected the entire table including the column headings; there was no need to change this, so I clicked Next again.
    I clicked Layout... to specify the layout of the pivot table.
    I dragged Name to the Row area, Type to the Column area, then Name to the Data area. Excel automatically created a button Count of Name.
    (You can change the caption by double clicking the button; you can change other settings as well but that isn't necessary here.)
    I clicked OK to return to the main dialog.
    I specified that I wanted to create the pivot table on the active sheet, and specified a cell to act as upper left corner.
    I clicked Finish to let Excel create the pivot table.

    You can find many pivot table tutorials on the web (use Google), for example:
    How to use PivotTables
    Excel 2000 Tutorial: PivotTable Reports 101

  7. #7
    New Lounger
    Join Date
    Oct 2008
    Location
    Australia
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting instances (Excel 2003)

    re:739,907 from broome
    When setting up a pivot table, excel is not auto creating a button Count of Name. I've hunted thru but can't seem to create a button with a count formula.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Counting instances (Excel 2003)

    When you click Layout... in step 3 of the Pivot Table Wizard, and drag one of the fields from the right hand side to the DATA area in the middle, Excel will create a Count of <field> button for a text field, or a Sum of <field> button for a number field. When you double-click a button in the DATA area, you can change its caption and select another summary function if desired, plus other options.

  9. #9
    New Lounger
    Join Date
    Oct 2008
    Location
    Australia
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting instances (Excel 2003)

    Thank you but it's stil not happening.
    Could it be that the data I'm trying to count is on a different worksheet to the pivot table? Shouldn't be!

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Counting instances (Excel 2003)

    Could you attach a stripped down copy of your workbook (zipped if necessary), with an indication of what you're trying to do?

  11. #11
    New Lounger
    Join Date
    Oct 2008
    Location
    Australia
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting instances (Excel 2003)

    It's taken a while to get back to you. Attached is a small sample of my excel file.
    I would like to be able to count how many REP1 vs TYPE

    ie. BRM = How many "House", BRM = How many "Land", PRD = how many "land" etc.
    Attached Files Attached Files

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Counting instances (Excel 2003)

    - Click anywhere in the table, e.g. in A1.
    - Select Data | Pivottable and Pivotchart Report...
    - Click Next >.
    - Excel automatically selects the entire table. Click Next > again.
    - Click Layout...
    - Drag Rep1 to the Row area.
    - Drage TYPE to the Column area.
    - Drag TYPE to the Data area. You should see Count of TYPE.
    - Click OK to return to the Pivot Table Wizard.
    - Specify where you want the pivot table, then click Finish.
    - See the attached version.
    Attached Files Attached Files

  13. #13
    New Lounger
    Join Date
    Oct 2008
    Location
    Australia
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting instances (Excel 2003)

    Yee HAA!!!

    This is working really well. If I need to change the data or add extra information will I need to redo the pivot table or will it auto update?

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Counting instances (Excel 2003)

    Pivot tables don't update automatically. Click anywhere in the pivot table and click the Refresh Data button on the pivot table toolbar (the exclamation mark) to update it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •