+ Reply to Thread
Results 1 to 5 of 5

Pivot Table count of multiple columns with same text

  1. #1
    Registered User
    Join Date
    08-11-2012
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2010
    Posts
    9

    Pivot Table count of multiple columns with same text

    Hi,

    I essentially have a table of thousands of rows of data. I need to count several columns that will contain the same text, for example
    apples oranges strawberries
    rotten juicing
    fresh
    rotten
    juicing, fresh
    fresh

    etc.

    I wish to count, for each type of fruit, the number of entries that are each of the combinations: rotten, juicing, fresh. (I want all combinations, so any like "juicing, fresh" should be included in the pivot table as well, something like this:

    apples oranges strawberries Total
    rotten 1 1 2
    juicing 1 1
    fresh 2 2
    juicing, fresh 1 1
    Total 2 2 2 6


    Given ultimately there are about 40 columns that I will eventually need to count, I don't want to create a pivot table for each column (which is the only solution I can come up with at the moment)

    So is it possible to create the above (single) pivot table???

    Thanks

  2. #2
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Pivot Table count of multiple columns with same text

    Could you attach a sample file?
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Pivot Table count of multiple columns with same text

    use this formula (adjust ranges as needed):

    =SUMPRODUCT((LEN(A$3:A$12)-(LEN(SUBSTITUTE(A$3:A$12,$F3,""))))/LEN($F3))
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  4. #4
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Pivot Table count of multiple columns with same text

    Nice one Glenn

  5. #5
    Registered User
    Join Date
    08-11-2012
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Pivot Table count of multiple columns with same text

    Thanks Glen,

    I was fixated on a Pivot Table and didn't even consider something like this.

    Great help!!

    Trev

+ 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. Replies: 6
    Last Post: 08-19-2015, 07:46 PM
  2. Count frequency from multiple columns, pivot table
    By puay in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 07-30-2014, 03:07 AM
  3. Combining value count from multiple columns in pivot table
    By AFsimRA in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 02-05-2013, 11:29 AM
  4. Replies: 0
    Last Post: 10-16-2012, 05:17 AM
  5. Pivot Table - Count data From Multiple Columns
    By Ashraf1 in forum Excel General
    Replies: 21
    Last Post: 02-23-2011, 08:13 AM
  6. Replies: 0
    Last Post: 01-29-2010, 06:40 PM
  7. Pivot Table - How do I count number of columns?
    By Vicky in forum Excel General
    Replies: 5
    Last Post: 08-08-2006, 08:30 AM

Tags for this Thread

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