+ Reply to Thread
Results 1 to 3 of 3

Excel formula to sumproduct but with multiple conditions

  1. #1
    Registered User
    Join Date
    05-06-2014
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    2

    Cool Excel formula to sumproduct but with multiple conditions

    Hello!

    Can anyone advise on the following I have a long list of products (rows) and data columns for weight, price and units sold. I want to get to a single figure of unit which satisfy two criteria, falls within two given weight points and within two given price points.

    I have worked a formula to give a sum of products within two data points e.g. weight point 0-200g, using
    = SUMPRODUCT((WEIGHTLIST>=LOW WEIGHT CONDITION)*(WEIGHTLIST <=HIGH WEIGHT CONDITION))*(UNITS SOLD)
    =(SUMPRODUCT(($H$17:$H$5000>=A5)*($H$17:$H$5000<=B5)*$D$17:$D$5000))

    I would like to complicate (!) this by adding in an additional two data points (for a different column), which must also be satisfied e.g. price point between 0-3 and falls into the weight bracket 0-200g, any suggestions would be hugely appreciated!

    Thanks, Danielle

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,072

    Re: Excel formula to sumproduct but with multiple conditions

    Let's say this other column is J and you put low value (0) in C5 and high value (3) in D5. Just add those condtions to your existing formula.

    =(SUMPRODUCT(($H$17:$H$5000>=A5)*($H$17:$H$5000<=B5)*($J$17:$J$5000>$C$5)*($J$17:$J$5000 <= $D$5)*($D$17:$D$5000))
    Does that work?
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    05-06-2014
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Excel formula to sumproduct but with multiple conditions

    Absolutely fab! Thats worked a treat!

    Thanks!

+ 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. [SOLVED] Sumproduct with multiple Conditions
    By abhinavbinkar in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-09-2014, 06:40 AM
  2. Multiple (3) SUMPRODUCT conditions
    By timjames in forum Excel General
    Replies: 2
    Last Post: 11-03-2011, 03:17 PM
  3. Sumproduct - Using multiple conditions
    By EXCELNEWCOMER in forum Excel General
    Replies: 5
    Last Post: 02-11-2010, 04:43 PM
  4. Sumproduct - multiple conditions
    By rasonline in forum Excel General
    Replies: 10
    Last Post: 06-04-2009, 05:10 PM
  5. Sum with multiple conditions:SUMPRODUCT
    By will.00 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-06-2007, 03:51 AM

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