+ 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)

Similar Threads

  1. Need help with Sumproduct and Weighted Average - File Attached
    By sachinattri in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-20-2013, 02:30 PM
  2. conditional formating file attached
    By kiany406 in forum Excel General
    Replies: 8
    Last Post: 06-30-2011, 07:27 AM
  3. Conditional Formatting Formulas-attached a file
    By smart_as in forum Excel General
    Replies: 3
    Last Post: 03-14-2011, 04:19 PM
  4. conditional formating-file attached
    By afgi in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 11-09-2010, 04:26 PM
  5. Conditional Array & VLOOKUP: Example file attached
    By David Brown in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-09-2010, 09:43 AM
  6. Conditional Format Paste Problem - File Attached
    By Joe B in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-17-2009, 08:54 AM
  7. Conditional Formatting Help-attached file
    By jmessec in forum Excel General
    Replies: 1
    Last Post: 08-23-2007, 09:32 PM

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