+ Reply to Thread
Results 1 to 3 of 3

SUMPRODUCT that multiplies

  1. #1
    Registered User
    Join Date
    09-20-2020
    Location
    Indiana, USA
    MS-Off Ver
    Office 2016
    Posts
    2

    SUMPRODUCT that multiplies

    Hello, I was looking to do a kind of sumproduct function that multiplies the resulting values. Using the sumproduct i can identify specific rows that i want via the double negative criteria. However rather than adding up the product of the arrays, i'd like to multiply them. I couldn't get it to work with the PRODUCT formula and IF-AND criteria. In the attached image with criteria needing 3 Yes's, sumproduct would give me a value of 2, but I'd like to get a value of .25 (1*.5*.5). Help is much appreciated.Excel.png

  2. #2
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,366

    Re: SUMPRODUCT that multiplies

    Presumably you want the product of values in E5:E9 for which the corresponding values in B5:D9 are all Yes by row. You'd need to use an array formula for that.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    To enter an array formula, hold down [Ctrl] and [Shift] keys before pressing [Enter]. You could avoid array formula entry by using the less efficient

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Tangent: Windows 7 is NOT your MS Office version.

  3. #3
    Registered User
    Join Date
    09-20-2020
    Location
    Indiana, USA
    MS-Off Ver
    Office 2016
    Posts
    2

    Re: SUMPRODUCT that multiplies

    I wasn't able to make the array method work, but the other one did, thank you! Also, thanks for the correction, I'll be sure to update.

+ 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. Replies: 9
    Last Post: 11-14-2016, 07:39 PM
  2. if function that returns and multiplies
    By tilly2701 in forum Excel General
    Replies: 4
    Last Post: 02-23-2016, 01:03 PM
  3. [SOLVED] Formula that multiplies only every year
    By tryingtoexcelatexcel in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 06-08-2015, 05:35 AM
  4. [SOLVED] SUMPRODUCT multiplies too many times...?
    By excellenthelp in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-25-2013, 11:10 AM
  5. [SOLVED] Entering a percentage multiplies it by 100!
    By Tony Vargo in forum Excel General
    Replies: 8
    Last Post: 11-21-2012, 05:55 AM
  6. IF formula that also multiplies
    By amy22x3 in forum Excel General
    Replies: 5
    Last Post: 10-10-2007, 09:36 AM
  7. Formula that multiplies X for every Y unit
    By Dholden1 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-04-2007, 06:43 PM

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