+ Reply to Thread
Results 1 to 4 of 4

CONDITIONAL SUMPRODUCT function: average & max total value

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

    Question CONDITIONAL SUMPRODUCT function: average & max total value

    Hello,

    I have a worksheet with over 200K rows/data entries. This worksheet contains a list of titles and its sales performance by month - over a three month period. Not all titles have sales in each of three months, some had sales in 2 months or only 1.

    The worksheet lists the sales for each title by distributor by month.


    I need a "master" sumproduct function to get the SUM total value of all sales during the 3-month period *BUT* for only titles that had *cumulative* sales over 500.

    Here is my formula that needs adjusting:

    =SUMPRODUCT(('Raw Data'!$E$2:$E$228707=TITLE)*('Raw Data'!$C$2:$C$228707=DISTRIBUTOR)*('Raw Data'!$H$2:$H$228707>=500),'Raw Data'!$H$2:$H$228707)

    Unfortunately that formula "filters" individual titles with sales over 500 by individual month, and NOT the titles cumulative sales over 3 months that exceed 500.


    Once I have that conditional sumproduct formula.... I'll need another formula to calculate the average sales performance of titles with cumulative sales over 500. I'm assuming all I need to calculate the average is a DISTINCT/count Unique values function.


    IE:
    SUMPRODUCT/Unique values




    I'll then need a formula to calculate the MAX/HIGHEST cumulative value. IE: what was the highest MAX value of cumulative sales (title with the highest sales over 3 months).

    =MAX(IF(('Raw Data'!$E$2:$E$228707=TITLE)*('Raw Data'!$C$2:$C$228707=DISTRIBUTOR)*('Raw Data'!$H$2:$H$228707>=500),'Raw Data'!$H$2:$H$228707))

    again this formula is incorrect because it only looks for the individual highest value listing, and not the highest cumulative total value


    Thanks

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,008

    Re: CONDITIONAL SUMPRODUCT function: average & max total value

    It would help a lot if you could attach a sample worksheet. I can't visualize how your data is organized and I doubt other people can either.

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

    Re: CONDITIONAL SUMPRODUCT function: average & max total value

    Quote Originally Posted by dflak View Post
    It would help a lot if you could attach a sample worksheet. I can't visualize how your data is organized and I doubt other people can either.


    Please see the attached file

    https://www.hightail.com/download/ZW...NDJveE92eE1UQw

  4. #4
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,008

    Re: CONDITIONAL SUMPRODUCT function: average & max total value

    First, I cut the data down to 10,000 rows. I did not need the full 200K rows to work out the concept. Secondly I noted that each title had at most three months and made the assumption that there would be no additional data.

    I took the liberty of converting the data to an Excel Table. There are many advantages to manipulating data in an Excel Table. One of them is natural language type syntax suggested by intellisense for the formulas. So =SUMIFS(H2:H10000,F2:F10000,F2) , becomes =SUMIFS([Units Sold],[Video Title],[@[Video Title]]). Also tables know how many rows they have so you don't have to guess on how many rows to use in the formula. You are always working with the exact amount of data you need.

    This wiki has information on working with tables: http://www.utteraccess.com/wiki/inde...ables_in_Excel

    You can get more information on SUMPRODUCT and array formulas here: http://www.utteraccess.com/wiki/inde...Array_Formulas

    The issue is based on creating the criteria where the three month's sales is > 500 units. I did this with a helper column (Column I). It has the formula =SUMIFS([Units Sold],[Video Title],[@[Video Title]]) This column is used as a "filter" in the formulas although it isn't actually summed or counted anywhere.

    From my archives I pulled out how to do a conditional aggregate using array formulas: http://www.utteraccess.com/wiki/inde...inIf_AverageIf

    P.S. I worked all this out and you have the formula solution in Columns M:N on the Raw Data page. Then I realized that with one tiny additional step there was a simpler solution: a pivot table! I added one more helper column, Column J that determines if the cumulative total is over 500. I use this as the filter for the pivot table. Duh! Let Excel do the heavy lifting.

    Oh well, I got a refresh course and maybe you learned something you didn't need to know about array formulas to solve this problem. One of the nice things about Excel is that there are usually multiple ways to solve the same problem.
    Attached Files Attached Files

+ 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