+ Reply to Thread
Results 1 to 8 of 8

Return top Items based on the sum of associated values,but do not include blank cells

  1. #1
    Registered User
    Join Date
    12-05-2018
    Location
    Dallas
    MS-Off Ver
    2016
    Posts
    25

    Return top Items based on the sum of associated values,but do not include blank cells

    Hello,

    I am new here. I am trying to Return top 10 Items based on the sum of associated values, but not include the sum of blank cells.. For example: Item A has a total of 345 and Item D has a total of 312 and the blank cells have a total of 1071. The formula I am using goes as such {=IFERROR(INDEX(B$1:B$9,MATCH(1,(COUNTIF(G$1:G2,B$1:B$9)=0)*(LARGE(SUMIF(B$1:B$9,B$1:B$9,D$1:D$9)*(MATCH(B$1:B$9,B$1:B$9,0)=ROW(B$1:B$9)-ROW(B$1)+1),F3)=SUMIF(B$1:B$9,B$1:B$9,D$1:D$9)),0)),"")}. It works to put the items in order based off of summed values but it still includes the blanks. For example: A should be the top and D should be next, but the blank cells lead the way. I should mention that the Item column has formulas in it more specifically the "" in each formula. I am positive that is what is giving me problems.

    Please show me what I need to do to not include blank item cells.

    I have attached the document.
    Attached Files Attached Files

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Return top Items based on the sum of associated values,but do not include blank cells

    Hi and welcome to the forum

    I think I'd use a Pivot Table approach with a defined range name 'data' which automatocally adjusts to the size of the non blank data

    See ttached
    Attached Files Attached Files
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    12-05-2018
    Location
    Dallas
    MS-Off Ver
    2016
    Posts
    25

    Re: Return top Items based on the sum of associated values,but do not include blank cells

    I am happy to join the forum!

    Also, I get that is an option, but I am creating a document that automatically pulls new data based off of formulas so users won't have to continually refresh pivot tables. This is really the last formula and it's done.

  4. #4
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2019
    Posts
    2,360

    Re: Return top Items based on the sum of associated values,but do not include blank cells

    Put this on F2 and ENTERED as ARRAY Formulas, then copied down until blanks:

    =IFERROR(INDEX(CHOOSE({2\1},SUMIF($B$2:$B$8,$B$2:$B$8,$C$2:$C$11),$B$2:$B$8),MATCH(LARGE(IF(FREQUENCY(MATCH(
    SUMIF($B$2:$B$8,$B$2:$B$8,$C$2:$C$11)&$B$2:$B$8,SUMIF($B$2:$B$8,$B$2:$B$8,$C$2:$C$11)&$B$2:$B$8,),ROW($B$2:$B$8)-MIN(ROW($B$2:$B$8))+1),
    SUMIF($B$2:$B$8,$B$2:$B$8,$C$2:$C$11)+ROW($B$2:$B$8)/10000),ROWS(A$1:A1)),SUMIF($B$2:$B$8,$B$2:$B$8,$C$2:$C$11)+ROW($B$2:$B$8)/10000,),COLUMNS($A1:A1)),"")
    Attached Files Attached Files
    Last edited by azumi; 12-05-2018 at 10:29 PM.

  5. #5
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Return top Items based on the sum of associated values,but do not include blank cells

    Quote Originally Posted by Excelperson1992 View Post
    I am happy to join the forum!

    Also, I get that is an option, but I am creating a document that automatically pulls new data based off of formulas so users won't have to continually refresh pivot tables. This is really the last formula and it's done.
    OK, but users don't need to manually refresh PTs. YOU can use the Sheet Activate event to trigger an automatic refresh each time they go to look at the PT.

  6. #6
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Return top Items based on the sum of associated values,but do not include blank cells

    Please try at F1 Press Ctrl+Shift+Enter and copy down

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

  7. #7
    Registered User
    Join Date
    12-05-2018
    Location
    Dallas
    MS-Off Ver
    2016
    Posts
    25

    Re: Return top Items based on the sum of associated values,but do not include blank cells

    Thanks you guys so much!! Both of your formulas worked, Bo_ry your formula worked perfectly for what I'm looking for. You guys are real life savers! Have a great holiday!

  8. #8
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Return top Items based on the sum of associated values,but do not include blank cells

    Happy to help.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

+ 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. [SOLVED] Return last 4 values in a Row, ignoring blank cells
    By UnitedCloud01 in forum Excel General
    Replies: 6
    Last Post: 07-24-2017, 01:12 AM
  2. Formula that returns only values of cells and doesnt return blank/NA values
    By pageandrewr1 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-08-2016, 12:32 PM
  3. Replies: 3
    Last Post: 09-01-2014, 05:56 PM
  4. [SOLVED] ID first and last non blank values in a row and return that cells column header
    By hotelmrrsn in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-13-2014, 02:35 PM
  5. Return non-blank cells based on drop-down
    By j_harrison in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-17-2013, 04:13 PM
  6. Replies: 4
    Last Post: 03-15-2013, 01:03 PM
  7. Adding cells with values but if cells do not have values then return blank
    By jonnykhan in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-13-2012, 12:07 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