+ Reply to Thread
Results 1 to 5 of 5

Calculate the number of occurrence using Pivot Table

  1. #1
    Registered User
    Join Date
    02-08-2021
    Location
    singapore
    MS-Off Ver
    2017
    Posts
    2

    Calculate the number of occurrence using Pivot Table

    Hi everyone, please help me with the following:

    I'm trying to calculate the number of occurrence using Pivot Table. My data is as follow (sample)
    excel.png

    How can I use Pivot table add up the number of occurrence in both Shirt A and Shirt B.
    e.g.

    black - 'total sum'
    green - 'total sum'
    blue - 'total sum'


    Thanks everyone in advance!
    Attached Files Attached Files
    Last edited by tskhan; 02-08-2021 at 04:30 AM.

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Calculate the number of occurrence using Pivot Table

    Administrative note

    Welcome to the forum

    in your haste to solve your problem, you probably missed the yellow banner advising how to get answers faster by posting a sheet ?

    Please take a moment to read it and attach a sheet accordingly.

    Thanks you for helping us help you

  3. #3
    Registered User
    Join Date
    02-08-2021
    Location
    singapore
    MS-Off Ver
    2017
    Posts
    2

    Re: Calculate the number of occurrence using Pivot Table

    Hi Pepe, my apologies. I have attached a sample sheet with a sample final calculated results to be shown.
    Thanks much for highlighting. :D

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Calculate the number of occurrence using Pivot Table

    The data isn't in row over row arrangement (as shown below) that would be conducive to producing a pivot table.
    Shop Shirt Color
    1 A Black
    1 B Green
    2 A Green
    .
    .
    .
    9 B Green
    However the following formula will yield the expected results in B17:B20
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Calculate the number of occurrence using Pivot Table

    Using Power Query here is the Mcode

    Please Login or Register  to view this content.
    Excel 2016 (Windows) 32 bit
    A
    B
    1
    Value Addition
    2
    black
    2
    3
    blue
    5
    4
    green
    7
    5
    white
    4
    Sheet: Table1




    Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

    It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

    - Follow this link to learn how to install Power Query in Excel 2010 / 2013.

    - Follow this link for an introduction to Power Query functionality.

    - Follow this link for a video which demonstrates how to use Power Query code provided.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

+ 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 Calculate Field to divide total number of jobs by day type
    By quentin5000 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 09-05-2019, 10:16 PM
  2. Replies: 7
    Last Post: 02-27-2017, 07:41 AM
  3. Replies: 1
    Last Post: 11-18-2014, 11:53 AM
  4. Calculate number of days between two dates within a pivot table.
    By Old_Dog in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-03-2014, 07:38 AM
  5. [SOLVED] Calculate occurrence/thousand from table
    By karl8695 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-12-2013, 06:27 AM
  6. Replies: 6
    Last Post: 07-15-2013, 08:12 AM
  7. Calculate orders past agreed dates in pivot table and averages in pivot tables
    By applesandpears in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 10-18-2012, 05:26 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