+ Reply to Thread
Results 1 to 3 of 3

Counting Total Number of Customers that Purchased Fruit Items Listed in Multiple Columns

  1. #1
    Registered User
    Join Date
    West Des Moines, IA
    MS-Off Ver
    Microsoft Office 2007

    Counting Total Number of Customers that Purchased Fruit Items Listed in Multiple Columns


    Long time lurker who has appreciated many fixes the community has provided.

    I've finally come across something I couldn't describe succinctly enough to use in the search bar and I believe there is an easier answer than what I'm trying to accomplish.

    I have a data set which contains customers who made multiple orders. The order themselves are listed in each column and tied to the specific customer along with the date the order was made. I want to create a Pivot Table that tells me the total number of each type of fruit ordered broken into aging categories. I've attached a copy of an example dataset along with what I want the Pivot Table to look like (Desired Pivot Table tab) and what the actual Pivot Table looks like (Actual Pivot Table tab).

    I'm not sure if what I want to do is possible, but it has to only be done in Excel since this is the only program we have access to at my company. Is this possible?

    Thank you all in advance!
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    MS-Off Ver

    Re: Counting Total Number of Customers that Purchased Fruit Items Listed in Multiple Colum

    In stead of a pivot, why not just take your columns (apple, orange, banana, pear) and copy/paste-transpose them into rows on a clean tab. Then put your aging categories across the top in the columns and write a countifs formula to count the number of customer orders that meet the criteria of both being in the correct aging bucket as well as the right fruit?

    Does that make sense?

  3. #3
    Forum Guru
    Join Date
    MS-Off Ver
    Excel 2010/2019

    Re: Counting Total Number of Customers that Purchased Fruit Items Listed in Multiple Colum

    Hello Thor222 and Welcome to Excel Forum.
    Excel Pivot Tables are designed to work on row over row data as modeled in the green/white table.
    If your company is actually using the 2010 or later version you could produce the first three columns of the table by first turning the Customer:Pear columns of the original data into an Excel table and then using the following Power Query Advanced Editor Code:
    Please Login or Register  to view this content.
    Once that is done add the Days since purchase column populated by: =I$1-B11
    and the Aging category column using: =IF(D11<=30,"0-30 Days","31-45 Days")
    Note that I assume cell I1 will eventually be populated using =TODAY() but have manually placed 4/28/2021 in the cell so that the pivot table will display what is shown on the Desired Pivot Table sheet.
    For the Pivot Table on the Data Set sheet, place Produce in the Rows area and Aging Category in both the Columns and Values areas.
    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.

+ 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: 2
    Last Post: 05-03-2021, 12:18 PM
  2. [SOLVED] Total items purchased within fiscal year using a dropdown.
    By helmerr in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-19-2017, 03:28 PM
  3. [SOLVED] counting the total number of items which are ordered
    By ImTr0uBLe in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 05-16-2015, 12:03 PM
  4. [SOLVED] Counting number of items based on two columns
    By lukka in forum Excel General
    Replies: 3
    Last Post: 04-18-2012, 05:14 AM
  5. Replies: 4
    Last Post: 12-09-2011, 01:17 PM
  6. Replies: 1
    Last Post: 09-01-2010, 07:11 PM
  7. counting Total for customers that can appear more then once
    By jordan.lawrance in forum Excel General
    Replies: 3
    Last Post: 04-15-2009, 11:06 AM


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