+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 15 of 16

Conditional SUMPRODUCT

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

    Question Conditional SUMPRODUCT

    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.

    Link to document here:

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


    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 Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, 365 Insiders
    Posts
    11,314

    Re: Conditional SUMPRODUCT

    Can't tell from your description. Please try uploading direct to your post. The FAQs describe how. Many of us are reluctant to access downloads from 3rd part sites.
    Dave

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

    Re: Conditional SUMPRODUCT

    1 quick observation - using SP on that many rows is probably slowing your file way down. It may be better to use helper columns to run intermediate calcs, and then use a less resource-intense function.

    Please upload your file to the forum. Not all members are able - or willing - to access file-hosting sites
    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

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

    Re: Conditional SUMPRODUCT

    Hi FDibbins,

    I'm not able to attach directly. The file is 9MB

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

    Re: Conditional SUMPRODUCT

    we only need a small sample of what you are working with, just enough so we can give you want you need

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

    Re: Conditional SUMPRODUCT

    Got it... I shortened the raw data. And also compressed it.

    But I was not able edit the original post... So I was forced to repost the question:

    http://www.excelforum.com/excel-form...-attached.html

    Thanks

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

    Re: Conditional SUMPRODUCT

    Unable to open the file

    You can just upload the file here, in a new post

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

    Re: Conditional SUMPRODUCT

    Hi FDibbins,

    I did. I uploaded the file directly to the forum in a new post:

    http://www.excelforum.com/excel-form...-attached.html

    Thanks

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

    Re: Conditional SUMPRODUCT

    Yes, I know and I am unable to open that file

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

    Re: Conditional SUMPRODUCT

    I tried with another computer. The download worked

    Is the link broken for you? Or does it download, but can't open the Zip file?

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

    Re: Conditional SUMPRODUCT

    I reposed a newly created zip file


    Can you try downloading it again please?

    http://www.excelforum.com/excel-form...-attached.html

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

    Re: Conditional SUMPRODUCT

    The link is not broken, I can d/l the zip file, butthe zip file had an error when opening it

    Also, you need to try and upload future files on this thread here, not the otehr thread

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

    Re: Conditional SUMPRODUCT

    I was able to open that 1.

    Instead of SP, you should take a look at using the xxxIFs() range of functions, they are far more efficient than SP
    instead of...
    =SUMPRODUCT(('Raw Data'!$E$2:$E$30894=$K9)*('Raw Data'!$C$2:$C$30894=L$8)*('Raw Data'!$H$2:$H$30894>=$K$8))
    I used...
    =COUNTIFS('Raw Data'!$E:$E,TABLE!$K9,'Raw Data'!$C:$C,TABLE!L$8,'Raw Data'!$H:$H,">="&TABLE!$K$8)

    You could do the sane for SUM...
    =SUMIFS('Raw Data'!$H:$H,'Raw Data'!$E:$E,TABLE!$K9,'Raw Data'!$C:$C,TABLE!L$8,'Raw Data'!$H:$H,">="&TABLE!$K$8)
    and average...
    =AVERAGEIFS('Raw Data'!$H:$H,'Raw Data'!$E:$E,TABLE!$K9,'Raw Data'!$C:$C,TABLE!L$8,'Raw Data'!$H:$H,">="&TABLE!$K$8)

    Other than that, not really sure what you wanted here?

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

    Re: Conditional SUMPRODUCT

    Thanks FDibbins, but How would I calculate the Max & median?

    Thanks

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

    Re: Conditional SUMPRODUCT

    Quote Originally Posted by FDibbins View Post
    I was able to open that 1.

    Instead of SP, you should take a look at using the xxxIFs() range of functions, they are far more efficient than SP
    instead of...
    =SUMPRODUCT(('Raw Data'!$E$2:$E$30894=$K9)*('Raw Data'!$C$2:$C$30894=L$8)*('Raw Data'!$H$2:$H$30894>=$K$8))
    I used...
    =COUNTIFS('Raw Data'!$E:$E,TABLE!$K9,'Raw Data'!$C:$C,TABLE!L$8,'Raw Data'!$H:$H,">="&TABLE!$K$8)

    You could do the sane for SUM...
    =SUMIFS('Raw Data'!$H:$H,'Raw Data'!$E:$E,TABLE!$K9,'Raw Data'!$C:$C,TABLE!L$8,'Raw Data'!$H:$H,">="&TABLE!$K$8)
    and average...
    =AVERAGEIFS('Raw Data'!$H:$H,'Raw Data'!$E:$E,TABLE!$K9,'Raw Data'!$C:$C,TABLE!L$8,'Raw Data'!$H:$H,">="&TABLE!$K$8)

    Other than that, not really sure what you wanted here?


    Hi FDibbins,

    the formula:
    =SUMIFS('Raw Data'!$H:$H,'Raw Data'!$E:$E,TABLE!$K9,'Raw Data'!$C:$C,TABLE!L$8,'Raw Data'!$H:$H,">="&TABLE!$K$8)

    is incorrect because it still suffers from the original problem with the SUMPRODUCT function. It's only including titles with sales over 500 - excluding instances where a title has sales under 500 in a particular month. In reality, even if a title had sales of 300, cumulative sales in 3 months could be well over 500. The qualifier should be cumulative sales over 500 for each title.

    Thank you

+ Reply to Thread
Page 1 of 2 1 2 LastLast

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