+ Reply to Thread
Results 1 to 6 of 6

Compute Sumproduct while Excluding product Variants

  1. 12-05-2018, 01:56 PM #1
    Pneumz
    Pneumz is offline
    Registered User
    Join Date
    12-05-2018
    Location
    Connecticut
    MS-Off Ver
    Google Sheets
    Posts
    6

    Compute Sumproduct while Excluding product Variants

    Hi I am trying to compute sales totals of specific products. I want to include all products title "Athletic Heather Grey Pullover", and multiply the quantity purchased by the price to create a total.
    That said, 3X-Large are priced higher so i want to exclude these values from the total.

    C2:C is Product Name
    D2:D is Product variant (Size)
    E2:E is Quanity
    F2:F is Price

    My formula is returning zeros

    =SUMPRODUCT(E2:E,F2:F*(AND(C2:C="Athletic Heather Grey Pullover",D2:D<>"Size:3X-Large")))

    For some reason my screen shot is not uploading. I hope this is enough information! Please help!
    Reply With QuoteRegister To Reply
  2. 12-05-2018, 02:26 PM #2
    Richard Buttrey
    Richard Buttrey is offline
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Compute Sumproduct while Excluding product Variants

    Hi and welcome to the forum
    Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

    Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

    The attach icon isn;t working at the moment. Choose Go Advanced and look underneath the post for the Manage Attachments option
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.
    Reply With QuoteRegister To Reply
  3. 12-05-2018, 02:33 PM #3
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Compute Sumproduct while Excluding product Variants

    =SUMPRODUCT(E2:E * F2:F * (C2:C="Athletic Heather Grey Pullover") * (D2:D<>"Size:3X-Large") )
    Entia non sunt multiplicanda sine necessitate
    Reply With QuoteRegister To Reply
  4. 12-05-2018, 02:39 PM #4
    Pneumz
    Pneumz is offline
    Registered User
    Join Date
    12-05-2018
    Location
    Connecticut
    MS-Off Ver
    Google Sheets
    Posts
    6

    Re: Compute Sumproduct while Excluding product Variants

    Thank You! That solved my problem!
    Reply With QuoteRegister To Reply
  5. 12-05-2018, 02:49 PM #5
    Pneumz
    Pneumz is offline
    Registered User
    Join Date
    12-05-2018
    Location
    Connecticut
    MS-Off Ver
    Google Sheets
    Posts
    6

    Re: Compute Sumproduct while Excluding product Variants

    So now that that problem is resolved, what is the formula to compute all items that include both 3x and the product and exclude the others.
    Reply With QuoteRegister To Reply
  6. 12-11-2018, 10:55 PM #6
    JeteMc
    JeteMc is offline
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,556

    Re: Compute Sumproduct while Excluding product Variants

    Since shg's formula resolved the former, I feel that the following will resolve the latter:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Looking for ON HAND inventory formula excluding damaged product
    By fabian_76 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-08-2018, 09:50 PM
  2. Sum Product (excluding a row)
    By batchy in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-21-2016, 10:11 AM
  3. [SOLVED] Avoiding Variants: Multiple Declarations per Line Assign Variants To All But Last Variable
    By joe31623 in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 12-08-2015, 11:35 AM
  4. [SOLVED] Sumproduct to compute weighted avg #VALUE
    By cartica in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-02-2015, 04:13 PM
  5. how to compute difference in date/time vs today excluding weekend, holidays, and non-work
    By princelijah in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-13-2015, 08:37 AM
  6. Sumproduct excluding an Array
    By Odysseus in forum Excel General
    Replies: 4
    Last Post: 12-16-2009, 12:42 PM
  7. Excluding #N/A results from a SUMPRODUCT
    By Iscariot in forum Excel General
    Replies: 2
    Last Post: 04-03-2009, 07:38 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1