+ Reply to Thread
Results 1 to 4 of 4

Index/Aggregate/row match with conditions, pivot summary replacement

  1. #1
    Forum Contributor
    Join Date
    02-13-2018
    Location
    USA
    MS-Off Ver
    2016
    Posts
    105

    Index/Aggregate/row match with conditions, pivot summary replacement

    I am trying to determine why this formula is not picking up the single time (once) that the COUNTIFS function is picking up this value, it is returning duplicate values. Formulas needing evaluated are in "FINAL" tab (columns C-F) on attached excel file.

    Please Login or Register  to view this content.

    I know this formula works or a formula almost exactly like this because I have tested it ON ANOTHER TAB

    Please Login or Register  to view this content.
    ACU.PNG

    I believe the issue is I need to modify something in this formula
    Please Login or Register  to view this content.
    the "AP and "1" are good and should just be returning the results once, not multiples.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,588

    Re: Index/Aggregate/row match with conditions, pivot summary replacement

    I found a couple of issues with the formula at the top of the post.
    The AGGREGATE(15,6,(ROW($2:$300000)) part should read AGGREGATE(15,6,(ROW($2:$300000)-1)
    At the end of the formula ROW(3:3) should read ROW(1:1)
    After making those changes the formula, applied to columns B:F on the Final sheet is finding only the three unique values from sheet2 that meet the criteria of "AP" in column U.
    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.

  3. #3
    Forum Contributor
    Join Date
    02-13-2018
    Location
    USA
    MS-Off Ver
    2016
    Posts
    105

    Re: Index/Aggregate/row match with conditions, pivot summary replacement

    @ JeteMC

    I can't believe I missed the -1, that fixed it

    thanks as always for your stellar feedback!

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,588

    Re: Index/Aggregate/row match with conditions, pivot summary replacement

    You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

+ 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. Huge data index/match replacement
    By teireii in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-31-2017, 03:33 AM
  2. Huge data index/match replacement
    By teireii in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-26-2017, 05:44 AM
  3. [SOLVED] Formula to find 2nd Match using Index and 2 Match conditions
    By Cra5h in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 05-24-2016, 11:52 AM
  4. [SOLVED] vba replacement for index match array
    By eculver in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-05-2015, 05:39 PM
  5. Replies: 2
    Last Post: 12-18-2014, 09:52 AM
  6. Replies: 13
    Last Post: 12-13-2012, 11:44 AM
  7. SumIf Index Match Summary Help
    By Merlin54k in forum Excel General
    Replies: 4
    Last Post: 12-21-2011, 12:46 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