+ Reply to Thread
Results 1 to 8 of 8

Issue dealing with simple excel issue - Formula and Pivot

  1. #1
    Registered User
    Join Date
    09-20-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    75

    Issue dealing with simple excel issue - Formula and Pivot

    Dear friends,

    Can anyone help me with a simple excel. I would like to count the number of customers under an industry segment, both using formula and using pivot.

    For example, from the excel list,

    Customer BMSI -Biomedical Sciences Institute under ACAD, count as 1.
    Customer BMSI -Biomedical Sciences Institute R under ACAD, count as 1 as well.
    Total shall be 2 for BMSI and BMSI-R under ACAD, but from pivot, I cannot see this number.

    Any formula or can I do it from pivot?

    Attached with the excel for your reference.

    Thank you so much for your kind assistance.


    Regards,
    Joey
    Attached Files Attached Files

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Issue dealing with simple excel issue - Formula and Pivot

    hi joey. could you explain why "Customer BMSI -Biomedical Sciences Institute under ACAD" is counted as one? in your Raw worksheet, it appears in row 18, 19, 36 and 37. so it should be 4? if so, go to your PivotTable Field List and drag the Customer field to the VALUES area. since it's made up of text, the default will do a count of the non-empty rows.

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  3. #3
    Registered User
    Join Date
    09-20-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    75

    Re: Issue dealing with simple excel issue - Formula and Pivot

    Hi Guru,

    Thanks for your reply.

    I want to count the same customer under the same segment as 1 count, so that there is no duplication of the same customer in a segment.

    What if I also want to see the customer name? I tried dragging the customer to the VALUES area in Pivot, it works, but I could not see my customers' name.

    Is there any formula to work on the raw file?
    =count..



    Regards,
    Joey

  4. #4
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Issue dealing with simple excel issue - Formula and Pivot

    i'm not really sure what you are trying to do. the PivotTable only shows what is inside your source data. so for eg, it does not have customer XYZ from Ind. hence, the PivotTable does not show that. whatever it shows, it will definitely be 1 count. so is there any difference what others should show?
    BMSI -Biomedical Sciences Institute
    BMSI -Biomedical Sciences Institute R
    Nanyang Technological University
    Nanyang Technological University-RRR
    Singapore Polytechnic

    the Customer field can be in both ROWS and VALUES area. i have uploaded an eg to show you. perhaps, you can show what are the desired answers you are hoping to see.

    if you want all to show as 1 and the subtotal to show the unique customer it has under Ind, then it could be easier to repeat the Ind field name. i forgot the tab name it should have in Excel 2010. but click on the PivotTable and 2 extra tabs will appear on top. try looking out for Report Layout -> Repeat All Item Labels. i did it for you in a 2nd PivotTable.
    Attached Files Attached Files

  5. #5
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Cool Re: Issue dealing with simple excel issue - Formula and Pivot

    one way
    you can install PowerPivot then in PT dialog window tick Add to Data Model, and in Value Filed - set Customer to Distinct Count. After that set layout to Tabular Form

    (I used benishiryo file)
    hope you'll see PT on RAW sheet with Distinct Count
    Attached Files Attached Files
    Last edited by sandy666; 12-14-2017 at 11:12 PM. Reason: file added

  6. #6
    Registered User
    Join Date
    09-20-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    75

    Re: Issue dealing with simple excel issue - Formula and Pivot

    Oh my god, you are so good with excel, Guru!

    Yes I tried the Repeat All Item Labels and it works. But how you managed to make the 2nd PivotTable appeared, within the same PivotTable sheet?

    Your formula is so advance and complex. My desire outcomes already shown in your formula, which is... ACAD Total of 5, PHAR Total of 10, SI Total of 1 etc...

    Is Pivot able to show this individual IND segment total?
    Last edited by joeyjee; 12-14-2017 at 11:46 PM.

  7. #7
    Registered User
    Join Date
    09-20-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    75

    Re: Issue dealing with simple excel issue - Formula and Pivot

    Quote Originally Posted by sandy666 View Post
    one way
    you can install PowerPivot then in PT dialog window tick Add to Data Model, and in Value Filed - set Customer to Distinct Count. After that set layout to Tabular Form

    (I used benishiryo file)
    hope you'll see PT on RAW sheet with Distinct Count

    Sure, thanks for your guidance, had learnt a lot from you. I think I will install the PowerPivot and try it out!

  8. #8
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Issue dealing with simple excel issue - Formula and Pivot

    So don't forget to install PowerQuery also These are two powerful things for excel
    (or upgrade 2010 to 2016 - there is everything built-in)
    Last edited by sandy666; 12-15-2017 at 12:17 AM.

+ 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. Pivot Table & Chart with Excel VBA - issue on mAcro
    By bsguna in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 07-14-2016, 06:30 AM
  2. Lookup Formula Issue, Search issue
    By kperitz in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 04-07-2014, 01:50 PM
  3. Excel for Mac Pivot Table Formatting Issue
    By semantics in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 03-28-2013, 02:34 PM
  4. Issue with seemingly simple SUM formula
    By jwesterfield in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 03-25-2013, 09:28 AM
  5. Excel Pivot issue
    By sabiresufali in forum Excel General
    Replies: 0
    Last Post: 09-14-2011, 12:49 AM
  6. Excel 2007 Pivot Chart Issue
    By ewanioc in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 06-22-2011, 09:36 AM
  7. Pivot Table issue in Excel 2007
    By ExcelInstructor in forum Excel General
    Replies: 1
    Last Post: 06-29-2006, 03:50 PM

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