Results 1 to 8 of 8

Thread: =sumproduct

  1. #1
    Board Regular
    Join Date
    Dec 2016
    Posts
    214
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default =sumproduct

    hate last minute change requests, but

    The code works fine on the worksheet where the data resides, see below

    =SUMPRODUCT((I3:I51="S")*(J3:M51=2))


    but won't work when code is on another worksheet

    =SUMPRODUCT((Entries!I3:I51="S")*(Entries!J3:M51=2))

    what am I missing? Maybe I should take a deep breath!

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,799
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: =sumproduct

    It works fine for me.
    - How is it not working? Are you getting an error or unexpected results?
    - Are you sure that you have spelled the sheet name correctly? Anything different, even an "extra" space, will cause it to fail.
    - Are your sure that this other sheet is in the same workbook?
    Last edited by Joe4; May 30th, 2019 at 10:40 AM.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    Board Regular
    Join Date
    Dec 2016
    Posts
    214
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: =sumproduct

    Ok I just try it on the worksheet where it should be, using another cell and it works

    I just cut it from the test cell and pasted it into the correct cell and its functioning. I did delete a Conditional format that was there, maybe the cause?

    Thanks as always for a quick reply!

  4. #4
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,799
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: =sumproduct

    Conditional Formatting shouldn't have any affect on it.

    You didn't say what the issue was, whether you were getting errors or unexpected results.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #5
    Board Regular
    Join Date
    Dec 2016
    Posts
    214
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: =sumproduct

    The formula would just display in the cell and not function

  6. #6
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,799
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: =sumproduct

    The formula would just display in the cell and not function
    That just means that you entered the formula as text, and not as a formula.
    That will happen if the column is formatted as text to begin with.
    Select the whole column, go to Format Cells, and change the format to General (that is typically the default).
    Then when you enter formulas, then will enter as formulas and not text.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  7. #7
    Board Regular
    Join Date
    Dec 2016
    Posts
    214
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: =sumproduct

    the "why" is almost as important as the results!

    thanks for pursuing!

  8. #8
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,799
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: =sumproduct

    You are welcome.
    Once I knew what you were seeing, it became evident right away what the problem was.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •