+ Reply to Thread
Results 1 to 7 of 7

SUMPRODUCT with embedded MAX/MEDIAN by Groups

  1. #1
    Registered User
    Join Date
    10-24-2013
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    27

    Unhappy SUMPRODUCT with embedded MAX/MEDIAN by Groups

    Hi Folks -- good evening!

    I have a spreadsheet with 5 columns: Month, Distro Partner, Product, Title, and Sales

    I need (was using sumproduct formula) to calculate: number of Titles (by product), total sales volume, average sales by product, title with highest cumulative sales across all months, and median sales number by product
    *BUT* the calculations should only include TITLES with a SUM TOTAL over 500 cumulative sales across all months (not sales over 500 in one month)


    I thought my SUMPRODUCT formula was working until I realized that it was not taking the sum total of each title across all months (multiple instances: one instance for each month). I need a formula that will allow me to find the SUMPRODUCT/MAX/MEDIAN of total sales for each title regardless of month.



    =SUMPRODUCT(($J3=$C$2:$C$25)*(J$2=$B$2:$B$25)*($E$2:$E$25>=500),$E$2:$E$25)


    Please see the attached file
    Attached Files Attached Files
    Last edited by collegeitdept; 01-08-2016 at 05:18 PM.

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

    Re: SUMPRODUCT with embedded MAX/MEDIAN by Groups

    This array formula (activated with Ctrl+Shift+Enter) will find the Max value:
    Please Login or Register  to view this content.
    This array formula will find the median value:
    Please Login or Register  to view this content.
    Here is a copy of your file with the formulas applied:
    Copy of Sumproduct Groupings.xlsx
    Let me know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Registered User
    Join Date
    10-24-2013
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    27

    Re: SUMPRODUCT with embedded MAX/MEDIAN by Groups

    Thank you!!

  4. #4
    Registered User
    Join Date
    10-24-2013
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    27

    Re: SUMPRODUCT with embedded MAX/MEDIAN by Groups

    Quote Originally Posted by JeteMc View Post
    This array formula (activated with Ctrl+Shift+Enter) will find the Max value:
    Please Login or Register  to view this content.
    This array formula will find the median value:
    Please Login or Register  to view this content.
    Here is a copy of your file with the formulas applied:
    Attachment 439218
    Let me know if you have any questions.

    Hi JeteMc

    Thanks for getting back to me... one slight adjustment. In addition to looking at the Product names, it needs to calculate at the Title level. So does each Title (across all month instances) sum total over 500.


    Please see the modified file. The correct values (what the formula should return) is in the table on the right in red text.

    Thanks

    Sumproduct Groupings 1.xlsx

  5. #5
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    8,898

    Re: SUMPRODUCT with embedded MAX/MEDIAN by Groups

    Quote Originally Posted by collegeitdept View Post
    In addition to looking at the Product names, it needs to calculate at the Title level. So does each Title (across all month instances) sum total over 500.
    I think that I have an idea of what you mean but it would help to see a sample of this table.
    Quote Originally Posted by collegeitdept View Post
    Please see the modified file. The correct values (what the formula should return) is in the table on the right in red text.
    I don't understand where those numbers come from. Take the value of 2 in cell O3 I would assume that to be the count of "Distributor Online" that is made up of "News Now". However looking at the data in columns B and C the count seems as if it should be 6. I will need to understand where the red numbers come from in order to write the formulas.

  6. #6
    Registered User
    Join Date
    10-24-2013
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    27

    Re: SUMPRODUCT with embedded MAX/MEDIAN by Groups

    For Distributor Online, News Now.... there are to Title names (aka SKUs) with sales over 500 (over all months). Their combined total sales is 3833, with an average of 958

    Think of Titles as SKUs.... they are the same Title even if they have multiple instances: because the same title has sales in month X and Month Y


    Does that explain it?

  7. #7
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    8,898

    Re: SUMPRODUCT with embedded MAX/MEDIAN by Groups

    I understand what you are looking for now. I want to show you a pivot table solution that partially supplies the information that you are looking for, albeit not in the form that you requested. The highlighted titles are those that have sales of more than 500 total. The 'Median' column is not actually part of the pivot table but the formula is copied down far enough to accommodate any of the three Distro Partners individually. Distro Partners can be selected in P10, the selection of more than one will temporarily disable the 'Median' column.
    Copy of Sumproduct Groupings.xlsx
    What you are looking for is probably doable, and if this doesn't work for you I'll come back to the problem later.
    Let me know if you have any questions.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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