+ Reply to Thread
Results 1 to 7 of 7

Calculating number of times individual number appears in a pivot table

  1. #1
    Registered User
    Join Date
    12-30-2010
    Location
    Huddersfield, England
    MS-Off Ver
    Excel 2010
    Posts
    58

    Calculating number of times individual number appears in a pivot table

    I have a excel document with several different numbers in column A, when i pivot the data and 'Count' this value it totals these values. I want it to only show this figure once

    e.g
    Number Name
    123 Joe
    123 Joe
    234 Mark
    234 Mark
    234 Mark
    999 Jackie
    567 Anne

    So i want to pivot table to show

    Joe - 1
    Mark - 1
    Jackie - 1
    Anne - 1

    At the moment its showing

    Joe - 2
    Mark - 3
    Jackie - 1
    Anne - 1

    Can any body help me???
    Last edited by snoopy1461; 10-15-2014 at 08:06 AM.

  2. #2
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    7,617

    Re: Calculating

    see attached file
    Attached Files Attached Files
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  3. #3
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX, 365 for windows, Windows 10 Insider
    Posts
    5,921

    Re: Calculating

    Put the name into the data section,

    then double click

    now change to choose Count

    that will count as you have it now - so 2 etc for each name
    to get to a unique value

    then use the TAB
    show values as
    and select the last one
    index
    Wayne

    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  4. #4
    Forum Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    11,135

    Re: Calculating

    Title adapted
    Last edited by Pepe Le Mokko; 10-15-2014 at 11:26 AM.
    Be very, very careful using IFERROR ! It hides ALL errors which is not always what you want to get correct results

  5. #5
    Registered User
    Join Date
    12-30-2010
    Location
    Huddersfield, England
    MS-Off Ver
    Excel 2010
    Posts
    58

    Re: Calculating number of times individual number appears in a pivot table

    Sorry for the short title, i forgot to go back and amend it before clicking submit

    Although they have been helpful, still not quite what i'm after, ive thought about it and is there a formula that can work out work out how many times the number appears and divide it by one. so you will get a table like

    Number Decimal Name
    123 0.5 Joe
    123 0.5 Joe
    234 0.33 Mark
    234 0.33 Mark
    234 0.33 Mark
    999 1 Jackie
    567 1 Anne

    So when the pivot it done it will total 1?

  6. #6
    Registered User
    Join Date
    12-30-2010
    Location
    Huddersfield, England
    MS-Off Ver
    Excel 2010
    Posts
    58

    Re: Calculating number of times individual number appears in a pivot table

    Sorry for the short title, i forgot to go back and amend it before clicking submit

    Although they have been helpful, still not quite what i'm after, ive thought about it and is there a formula that can work out work out how many times the number appears and divide it by one. so you will get a table like

    Number Decimal Name
    123 0.5 Joe
    123 0.5 Joe
    234 0.33 Mark
    234 0.33 Mark
    234 0.33 Mark
    999 1 Jackie
    567 1 Anne

    So when the pivot it done it will total 1?

  7. #7
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    7,617

    Re: Calculating number of times individual number appears in a pivot table

    see the attached file
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Calculating recurring date in following month, calculating # days in that period
    By Walterius in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-06-2005, 03:05 AM
  2. [SOLVED] Calculating recurring date in following month, calculating # days in that period
    By Walterius in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-06-2005, 02:05 AM
  3. Replies: 6
    Last Post: 09-05-2005, 11:05 PM
  4. Replies: 6
    Last Post: 09-05-2005, 10:05 PM
  5. Replies: 0
    Last Post: 03-15-2005, 10:06 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1