+ Reply to Thread
Results 1 to 7 of 7

Formula using Index, Aggregate, Row, Count, Sum, Sumif not giving correct sum of values

  1. #1
    Registered User
    Join Date
    07-21-2020
    Location
    Washington, DC
    MS-Off Ver
    365
    Posts
    20

    Formula using Index, Aggregate, Row, Count, Sum, Sumif not giving correct sum of values

    I am using a formula to sum the values in the column if it matches a certain criteria but it is not giving the answer I want. Could you help me figure out what I'm doing wrong? I have a table named Prod with columns labeled Tag and Group. Here is the formula I have in B20: =SUM(SUMIF($A$3:$A$10,INDEX(Prod[Tag],AGGREGATE(15,6,1/(Prod[Group]=A20)*ROW(Prod[Group]),ROW(INDEX($A:$A,1):INDEX($A:$A,COUNT(1/(Prod[Group]=A20)*ROW(Prod[Group])))))),$B$3:$B$10))

    help 1.png The table written in red is my desired outcome.
    Help 2.png Here is the table of how I want to seperate my data. It is in a seperate sheet.
    Last edited by planjb; 07-24-2020 at 08:57 AM. Reason: changing title

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,980

    Re: Why is my formula not giving the desired value? (Index, Aggregate, Row, Count, Sum, Su

    1. Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

    Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

    Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

    (Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

    2. Please see the yellow banner above on how to upload a sample workbook
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    07-21-2020
    Location
    Washington, DC
    MS-Off Ver
    365
    Posts
    20

    Re: Formula using Index, Aggregate, Row, Count, Sum, Sumif not giving correct sum of value

    Hi Ford, I hope that this new title is more informative. Thank you for letting me know. Best, planjb

  4. #4
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    UK
    MS-Off Ver
    various
    Posts
    1,883

    Re: Formula using Index, Aggregate, Row, Count, Sum, Sumif not giving correct sum of value

    You could change tack, and use SUMPRODUCT - e.g.:

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    it is a little simpler syntax wise, and will obviously adjust to do each day without modification but, it is less efficient in some respects (as conducts SUMIF for each Tag)
    so, if, in real-life, your Prod table is huge - post back.
    Last edited by XLent; 07-23-2020 at 09:26 AM.

  5. #5
    Registered User
    Join Date
    07-21-2020
    Location
    Washington, DC
    MS-Off Ver
    365
    Posts
    20

    Re: Formula using Index, Aggregate, Row, Count, Sum, Sumif not giving correct sum of value

    Thank you for your help! I'm not sure if this would be the best way to solve my problem since my prod table will be over 90 rows long with a lot more groups than my example. However, I'm just curious... for C20, should the formula be that? The formula is giving me the answer as 24 when it should be 30.

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  6. #6
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    UK
    MS-Off Ver
    various
    Posts
    1,883

    Re: Formula using Index, Aggregate, Row, Count, Sum, Sumif not giving correct sum of value

    I edited the earlier post I think, a few mins after posting, so it's possible you're using the pre-edit version but...
    w.r.t the formula in post 2 -- that can be applied to entire result matrix (B20:H22) without need for adjustment
    (the use of COLUMNS with a 2 column increment, ensures that as it is dragged to the right the SUMIF uses A:B, then C:D, then E:F etc)

    In terms of the Product list -- I'd suggesting testing this approach to benchmark as whilst the 90 Products isn't great (for this approach) there is an overhead with the dynamic AGGREGATE route too - so worth having a performance 'comparison'.
    Last edited by XLent; 07-24-2020 at 04:14 AM.

  7. #7
    Registered User
    Join Date
    07-21-2020
    Location
    Washington, DC
    MS-Off Ver
    365
    Posts
    20

    Re: Formula using Index, Aggregate, Row, Count, Sum, Sumif not giving correct sum of value

    I just tried this with my real excel and it works! Thank you so much for your help!

+ 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. INDIRECT function on an INDEX / AGGREGATE Formula
    By RLONG98 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-09-2020, 05:56 PM
  2. Issue in Index/Aggregate formula
    By QSGuy in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-18-2019, 09:13 AM
  3. [SOLVED] Index/Aggregate formula calculates correct row but does not yield name from Index array
    By FivestarMac in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-17-2019, 05:07 PM
  4. Replies: 1
    Last Post: 08-17-2019, 01:11 PM
  5. [SOLVED] using aggregate with index giving num error ??
    By JEAN1972 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 03-28-2019, 10:59 AM
  6. An Aggregate and Index Formula Issue
    By francoiscj1 in forum Excel General
    Replies: 5
    Last Post: 11-19-2018, 09:08 PM
  7. [SOLVED] SUMIFS Formula not giving desired result
    By lukemelville in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-12-2013, 09:15 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