+ Reply to Thread
Results 1 to 2 of 2

Conditional Sumproduct (with file attached)

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

    Conditional Sumproduct (with file attached)

    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.


    attached file:


    Marketing - Sales by Poster Titles and Distributor.zip


    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
    Last edited by collegeitdept; 01-06-2016 at 09:42 PM.

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

    Re: Conditional Sumproduct (with file attached)

    Bumping up thread

+ 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