+ Reply to Thread
Results 1 to 5 of 5

logic behind sumproduct

  1. #1
    Forum Contributor
    Join Date
    08-14-2013
    Location
    LA Baby!!
    MS-Off Ver
    Excel 2007
    Posts
    1,598

    logic behind sumproduct

    Could someone explain the logic behind this formula? Pretty much how it works. Thanks

    =SUMPRODUCT(--($C$2:$C$22=1),--($E$2:$E$22>=$D$2:$D$22))

  2. #2
    Valued Forum Contributor
    Join Date
    01-19-2012
    Location
    Barrington, IL
    MS-Off Ver
    Excel 2007/2010
    Posts
    1,213

    Re: logic behind sumproduct

    I typed all of this out already, then Excelforum decided it wanted to lock up and delete everything, to teach me the virtue of patience.


    Anyways, here we go again.

    It is an array formula. It gives you the Summed product of two arrays, so So sumproduct(a1:a3,b1:b3) would do this:
    SUM(a1*b1,a2*b2,a3*b3)
    Or,


    In your example formula, it would do this

    {c2=1, c3=1, c4=1,......} all the way to c22. Then result would be like the following,
    {TRUE,TRUE,FALSE,TRUE,TRUE...}
    Then you put a double negative in, --, which converts it to a 1 or a 0 depending on TRUE or FALSE
    {1,1,0,1,1...}

    Then the other part,
    {e2>=d2, e3>=d3, e4>=d4...e22>=d22}
    it will also return true's and falses, lets say
    {1,0,1,0,1...}

    Now, we multiple them, which is the PRODUCT part

    {1*1,1*0,0*1,1*0,1*1...}
    which is
    {1,0,0,0,1...}

    Then we SUM those 0's and 1's to get the end result, which in this case is
    1+0+0+0+1 = 2

    Hope this helps!~



    Edit; technically those should be semicolons between the values and not commas, oh well.

    Semicolon notes new row, comma is new column

    so {0,1;2,3;4,5} would represent a table of

    0 1
    2 3
    4 5
    Last edited by Speshul; 09-16-2014 at 02:39 PM.
    You should hit F5, because chances are I've edited this post at least 5 times.
    Example of Array Formulas
    Quote Originally Posted by Jacc View Post
    Sorry, your description makes no sense. I just made some formula that looks interesting cause I had nothing else to do.
    Click the * below on any post that helped you.

  3. #3
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,769

    Re: logic behind sumproduct

    Quote Originally Posted by ammartino44 View Post
    Could someone explain the logic behind this formula? Pretty much how it works.
    =SUMPRODUCT(--($C$2:$C$22=1),--($E$2:$E$22>=$D$2:$D$22))
    The formula says: count the number of rows 2 through 22 where the value in column C equals 1 and the value in column E equals the value in column D.

    The expression $C$2:$C$22=1 returns an array of TRUE or FALSE. So does the expression $E$2:$E$22>=$D$2:$D$22.

    The double-negate (--) converts each to an array of 1 for TRUE and 0 for FALSE because otherwise, SUMPRODUCT would ignore the TRUE and FALSE logic values.

    Any equivalent arithmetic would do the same; for example, ($C$2:$C$22=1)*1 and ($C$2:$C$22=1)+0. So alternatively, we could write
    =SUMPRODUCT(($C$2:$C$22=1)*($E$2:$E$22>=$D$2:$D$22))

    In either case, SUMPRODUCT multiplies the array values row-by-row, which results in an array of 1 where both conditions are TRUE and 0 where either or both conditions are FALSE.

    The sum of the resulting array is effectively a count of when both conditions are TRUE.
    Last edited by joeu2004; 09-16-2014 at 02:55 PM.

  4. #4
    Registered User
    Join Date
    09-17-2014
    Location
    Munich, Germany
    MS-Off Ver
    2007
    Posts
    4

    Re: logic behind sumproduct

    Hi,

    Maybe you will be able to help me. I have small issue, the same one as I found here: http://www.excelforum.com/excel-prog...osed-file.html

    I used formula SUMIFS and it works perfectly but I used two separate files so when I close one I have error.

    Solution was to change formula for SUMPRODUCT but unfortunately I don't know how.

    Can you please help to change my formula from SUMIFS to SUMPRODUCT:

    =SUMIFS('[File Name.xlsx]Sheet Name'!H:H;'[File Name.xlsx]Sheet Name'!$A:$A;$A4;'[File Name.xlsx]Sheet Name'!$B:$B;$B4;'[File Name.xlsx]Sheet Name'!$D:$D;$D4)

    I will be very Grateful for you answer!
    Thanks.

  5. #5
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,904

    Re: logic behind sumproduct

    @J.U.

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

+ 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 OR Logic
    By daffodil11 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-17-2014, 09:27 PM
  2. [SOLVED] SUMPRODUCT. using AND-OR logic with text
    By FAL22 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-26-2013, 04:34 PM
  3. SUMPRODUCT with multiple logic and without duplicates
    By cberas in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-25-2013, 03:53 PM
  4. formula logic (sumproduct,countif)
    By TROBFP in forum Excel General
    Replies: 1
    Last Post: 04-22-2007, 08:48 PM
  5. Replies: 3
    Last Post: 03-28-2006, 03:56 AM

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