+ Reply to Thread
Results 1 to 3 of 3

Pivot table displaying item name and how many item numbers were used

  1. #1
    Registered User
    Join Date
    05-17-2010
    Location
    New York, USA
    MS-Off Ver
    Excel 2010
    Posts
    44

    Pivot table displaying item name and how many item numbers were used

    I need to setup a pivot table to see the Item-Name and how many times the Item was ordered.

    The source:
    Column A - Date-Of-Order
    Column B - Item-Name
    Column C - Item-Number

    Each time a customer orders the item, the table makes several rows, one per each item. Therefore, you will have 3 rows if customer orders 3 one-inch nails (Item Number) - Item-Name is Nails. Each row will repeat one item-number (same in 3 rows) and Item-Name (same in 3 rows). The date-Of-Order will also repeat.

    Is customer order today 3 one-inch nails and next week and order of 5 one-inch nails, then I want to see the counter showing 2 (NOT 8), since here were 2 separate orders. The break will be the date. Different Date-Of-Order is the counter.

    The table should look like this and show how many times the item was ordered (and not the number of the items ordered):

    Nails
    1-inch ordered 2 times
    2-inch ordered 1 times
    3-inch ordered 1 times
    4-inch ordered 3 times

    Screws
    1-inch ordered 3 times
    1.2-inch ordered 1 times
    1.5-inch ordered 2 times
    Last edited by vicstone; 12-03-2019 at 02:28 PM. Reason: Additional Info

  2. #2
    Registered User
    Join Date
    05-17-2010
    Location
    New York, USA
    MS-Off Ver
    Excel 2010
    Posts
    44

    Re: Pivot table displaying item name and how many item numbers were used

    Test file uploaded
    Attached Files Attached Files

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

    Re: Pivot table displaying item name and how many item numbers were used

    This proposal employs a pivot table as a helper or calculation engine.
    Note that the pivot table could be moved and/or hidden for aesthetic purposes.
    The pivot table is set up with item numbers and descriptions in the row field, dates in the column field and descriptions (count) in the values field.
    The formulas that populate the output table on sheet 2 are:
    For description: =IF(Sheet1!H5="","",Sheet1!H5)
    For number of orders: =IF(A2="","",COUNTIFS(Sheet1!I5:S5,">0"))
    Let us know if you have any questions.
    Attached Files Attached Files
    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. VBA code to create pivot for single pivot item, if required item is not available cre
    By Ishwarind in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-04-2016, 11:06 AM
  2. [SOLVED] Creating a formula in a pivot table while using another calculated item in the pivot
    By mvhs1073933 in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 08-10-2015, 12:44 PM
  3. [SOLVED] Pivot table $ of each row item
    By ammartino44 in forum Excel General
    Replies: 1
    Last Post: 02-02-2015, 07:10 PM
  4. UserForm: Search Item in List > Call Table associated with Item > Execute Command
    By TexasAggie12 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-25-2014, 12:58 PM
  5. Replies: 5
    Last Post: 07-26-2011, 10:35 PM
  6. Replies: 0
    Last Post: 03-22-2009, 07:17 PM
  7. Pivot Item: removing non-existant item from the drop down
    By CinqueTerra in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-23-2006, 04: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