+ Reply to Thread
Results 1 to 5 of 5

A reverse SUMIF (Or: Finding value whose sumif = a certain number)

  1. #1
    Registered User
    Join Date
    12-04-2014
    Location
    London, England
    MS-Off Ver
    2012
    Posts
    2

    Unhappy A reverse SUMIF (Or: Finding value whose sumif = a certain number)

    First time poster... at my wit's end here.

    I have a sheet of data like the following:

    A(Category) B(Sales)
    Superbowl 10000
    World Series game 5000
    World Series game 5001


    What I am trying to do is, in one cell, get the category from A with the most sales from column B, and then in another cell get the sum of those sales for that category. So essentially ranking the Categories in terms of most sales and then getting the summed sales numbers.

    I have found this to be harder than it seems. I have had to go about it the reverse way - first getting the summed values with an array formula:

    ex: {=Large(IF(A:A=A:A,B:B),1)}

    And that seems to work. But I don't know how to then get the value in A that it is referring to!

    Any ideas? Or different ways to go about it (that aren't a pivot table. I know that's the obvious answer here but doesn't work for my purposes)

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,609

    Re: A reverse SUMIF (Or: Finding value whose sumif = a certain number)

    Have you tried to use pivot table for this?
    Shall do all ranking in just few mouse clicks.
    Important part: in Pivot table categories -> do sorting descending on sum of values
    Best Regards,

    Kaper

  3. #3
    Registered User
    Join Date
    12-04-2014
    Location
    London, England
    MS-Off Ver
    2012
    Posts
    2

    Re: A reverse SUMIF (Or: Finding value whose sumif = a certain number)

    Thanks for your reply!

    Yes, as I mentioned in the original post, pivot tables just aren't suitable in this workbook I know that's by far the easiest way!

    Alec

  4. #4
    Registered User
    Join Date
    08-10-2011
    Location
    TX
    MS-Off Ver
    Excel 2013
    Posts
    8

    Re: A reverse SUMIF (Or: Finding value whose sumif = a certain number)

    Assuming you have a finite number of potential categories you could mimic a pivot table by creating a complete list of categories, then SUMIF on each category to add-up all the values, then sort the results manually.

    Very manual, but would get you the answer I think you are looking for.
    If I helped, click on the star below the post!

  5. #5
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,609

    Re: A reverse SUMIF (Or: Finding value whose sumif = a certain number)

    Another possibility - create PT on the fly with a macro, copy it's result in your sheet and delete PT.
    Sorry, I've not noticed the phrase on PT in your original post.
    As a matter of fact - may be formulas are not that hard, but honestly (cited from http://www.excelforum.com/forum-rule...rum-rules.html ):
    Post a WORKBOOK. Nobody wants to type data from a picture or paste text from your post into a spreadsheet as a prelude to helping. To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.
    so show in a workbook some dummy data and expected (manually assessed) results.

+ 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] Shorten SUMIF + SUMIF + SUMIF each with same reference range but different criteria???
    By HeyInKy in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-13-2014, 09:25 PM
  2. SUMIF or SUMIF's formula with multiple criteria
    By jackiemariee in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-30-2013, 06:19 PM
  3. Reverse finding matches to a number
    By arnaudmanir in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 09-28-2013, 05:39 AM
  4. SUMIF not finding everything
    By jwright650 in forum Excel General
    Replies: 3
    Last Post: 02-15-2011, 04:20 PM
  5. Nested SUMIF statement or multiple SUMIF's
    By Dan27 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-17-2009, 06:55 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