+ Reply to Thread
Results 1 to 5 of 5

unable to grasp SUMPRODUCT

  1. #1
    Registered User
    Join Date
    04-03-2006
    Location
    South Dakota
    Posts
    5

    unable to grasp SUMPRODUCT

    I have different sales offices in sheet1 column E E2 thru E8
    I have revision numbers in sheet1 column I I2 thru I8

    Data as follows

    E2=NF I2=emptycell
    E3=DP I3=40619
    E4=DP I4=emptycell
    E5=NU I5=emptycell
    E6=DP I6=40609
    E7=DP I7=emptycell
    E8=SF I8=40618

    Sheet2 column A are my offices

    A2=DP
    A3=NU
    A4=SF
    A5=NF

    In sheet2 column c I need excel to enter a number to indicate how many times an office gets a revision number. Correct answers for above example should be DP=2 NU=0 SF=1 NF=0. But I cannot seem to get the formula right. I keep getting #NUM or # VALUE and so on. Would appreciate any help thank you.

    Todd

  2. #2
    Registered User
    Join Date
    04-03-2006
    Posts
    14

    Sumpproduct

    Hi

    try:
    for DP
    =SUMPRODUCT(((Sheet1!$E$2:$E$8)="DP")*1,(ISNUMBER(Sheet1!$I$2:$I$8))*1)

    for the others offices replace DP by NU

    =SUMPRODUCT(((Sheet1!$E$2:$E$8)="NU")*1,(ISNUMBER(Sheet1!$I$2:$I$8))*1)

    and so on. Or better, use an cell reference.

    Please note the "*1" (multiply by 1). That's to help Excel to convert the TRUE (or FALSE) result of the (Sheet1!$E$2:$E$8)="DP" evaluation to a numeric expresion (1 or 0) that can be added by sumproduct

  3. #3
    Conan Kelly
    Guest

    Re: unable to grasp SUMPRODUCT

    toddbob,

    Try this:

    Enter this formula in B2 on Sheet2 and Fill/Copy down
    =SUMPRODUCT((Sheet1!$E$2:$E$8=A2)*(Sheet1!$I$2:$I$8<>"")*1)

    I don't think that this is how the SUMPRODUCT function was meant to be used, but we use it this way all the time. Essentially this
    is saying "When the cells in the range E2:E8 on Sheet1 are equal to the value in A2 (Sheet2) AND the cells in the range I2:I8 on
    Sheet1 are not blank, then count them".

    I hope this helps,

    Conan Kelly




    "toddbob" <toddbob.25r98z_1144179903.427@excelforum-nospam.com> wrote in message
    news:toddbob.25r98z_1144179903.427@excelforum-nospam.com...
    >
    > I have different sales offices in sheet1 column E E2 thru E8
    > I have revision numbers in sheet1 column I I2 thru I8
    >
    > Data as follows
    >
    > E2=NF I2=emptycell
    > E3=DP I3=40619
    > E4=DP I4=emptycell
    > E5=NU I5=emptycell
    > E6=DP I6=40609
    > E7=DP I7=emptycell
    > E8=SF I8=40618
    >
    > Sheet2 column A are my offices
    >
    > A2=DP
    > A3=NU
    > A4=SF
    > A5=NF
    >
    > In sheet2 column c I need excel to enter a number to indicate how many
    > times an office gets a revision number. Correct answers for above
    > example should be DP=2 NU=0 SF=1 NF=0. But I cannot seem to get the
    > formula right. I keep getting #NUM or # VALUE and so on. Would
    > appreciate any help thank you.
    >
    > Todd
    >
    >
    > --
    > toddbob
    > ------------------------------------------------------------------------
    > toddbob's Profile: http://www.excelforum.com/member.php...o&userid=33112
    > View this thread: http://www.excelforum.com/showthread...hreadid=529773
    >




  4. #4
    Registered User
    Join Date
    04-03-2006
    Location
    South Dakota
    Posts
    5
    Both replys were very helpful and I have achieved what I wanted to do with your supplied formulas, Thank You

  5. #5
    JMB
    Guest

    RE: unable to grasp SUMPRODUCT

    for more information

    http://xldynamic.com/source/xld.SUMPRODUCT.html

    "toddbob" wrote:

    >
    > I have different sales offices in sheet1 column E E2 thru E8
    > I have revision numbers in sheet1 column I I2 thru I8
    >
    > Data as follows
    >
    > E2=NF I2=emptycell
    > E3=DP I3=40619
    > E4=DP I4=emptycell
    > E5=NU I5=emptycell
    > E6=DP I6=40609
    > E7=DP I7=emptycell
    > E8=SF I8=40618
    >
    > Sheet2 column A are my offices
    >
    > A2=DP
    > A3=NU
    > A4=SF
    > A5=NF
    >
    > In sheet2 column c I need excel to enter a number to indicate how many
    > times an office gets a revision number. Correct answers for above
    > example should be DP=2 NU=0 SF=1 NF=0. But I cannot seem to get the
    > formula right. I keep getting #NUM or # VALUE and so on. Would
    > appreciate any help thank you.
    >
    > Todd
    >
    >
    > --
    > toddbob
    > ------------------------------------------------------------------------
    > toddbob's Profile: http://www.excelforum.com/member.php...o&userid=33112
    > View this thread: http://www.excelforum.com/showthread...hreadid=529773
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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