Results 1 to 3 of 3

Thread: Count of Pivot Column Data determined from a HLOOKUP?

  1. #1
    Seeker Bl4ckPantha's Avatar
    Join Date
    May 2018
    Posts
    12
    Articles
    0
    Excel Version
    2016

    Count of Pivot Column Data determined from a HLOOKUP?



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi Guru's

    I have been handed a data set which cannot be well manipulated (data is kept on an external server and provided in the form of a Pivot Table, however only certain fields can be used as values).

    Essentially I am tying to perform a count of how many times one of the headers appears as a value against the Customer Name. With a normal pivot this is easy enough to do, as you just add the field you want counted into the value section of the Pivot Table, unfortunately this is not possible with this data.

    Example:

    The formula required in cell H6, will return a count of numerical values in the column of the header determined by G6. In this example I would like the formula to use "Remark 2" (G6) to find and perform a numerical count of column D (returning the value 3). If G6 = "ADENA GOLD" the formula would find the value in the pivot table and count the numerical values in column "B" (returning a value of 13).


    Name:  2018-05-23 10_17_59-Copy of CUBE - Wholesale  -  Repaired - Excel.png
Views: 93
Size:  29.6 KB


    Thanks for taking the time to read. If anything is unclear, please ask for clarification.

    Regards

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016
    Try:

    =COUNT(INDEX(B2:E17,,MATCH(G6,B1:E1,0)))


  3. #3
    Seeker Bl4ckPantha's Avatar
    Join Date
    May 2018
    Posts
    12
    Articles
    0
    Excel Version
    2016
    Thanks NBVC!

    That works perfect.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •