+ Reply to Thread
Results 1 to 12 of 12

Sum Product is showing 0 when i use f9 to evaluate, it shows 1.

  1. #1
    Registered User
    Join Date
    04-15-2015
    Location
    Oklahoma
    MS-Off Ver
    365
    Posts
    4

    Sum Product is showing 0 when i use f9 to evaluate, it shows 1.

    My sumproduct formula is
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    the purpose is to sum values if it occurs on the same week as the week put into A1 The cells C1:C3 are a string in the format "d/m/yy - d/m/yy" that is why I have the left and search. I left out the values because I believe the issues is with this portion. Whenever I use f9 to evaluate the formula, I get a count of 1 if i have 1 instance, but letting the formula run gives me 0. Capture.PNG




    Solved. I wasn't confirming with CTRL + SHIFT + ENTER

    Thanks for all of the help guys!
    Attached Files Attached Files
    Last edited by jeeke; 09-27-2019 at 12:43 PM. Reason: Solved

  2. #2
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,845

    Re: Sum Product is showing 0 when i use f9 to evaluate, it shows 1.

    Here's an ARRAY formula (applied by pressing Ctrl + Shift + Enter) that seems to work:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    I don't use SUMPRODUCT much, so not sure why that is not working.

    - Moo

  3. #3
    Registered User
    Join Date
    04-15-2015
    Location
    Oklahoma
    MS-Off Ver
    365
    Posts
    4

    Re: Sum Product is showing 0 when i use f9 to evaluate, it shows 1.

    I'm getting the same issue with yours. When I evaluate the formula through f9, I get 1. When I run the formula in the cell, I get 0.

  4. #4
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,845

    Re: Sum Product is showing 0 when i use f9 to evaluate, it shows 1.

    It works perfectly fine on my end. Not quite sure what is happening on yours.

    - Moo

  5. #5
    Registered User
    Join Date
    04-15-2015
    Location
    Oklahoma
    MS-Off Ver
    365
    Posts
    4

    Re: Sum Product is showing 0 when i use f9 to evaluate, it shows 1.

    I get 1 only if I am searching for the same thing that is in column C1. I don't think it's my settings because I can start a new document on excel online, copy the formula and I still end up with the same answer. I appreciate you trying to help me. I'm not sure what's going on.

  6. #6
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Sum Product is showing 0 when i use f9 to evaluate, it shows 1.

    You need to confirm your formula with Ctrl Shift Enter, not just Enter.

  7. #7
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,845

    Re: Sum Product is showing 0 when i use f9 to evaluate, it shows 1.

    I attached a file that shows it working just fine, using ARRAY formula entry (Ctrl + Shift + Enter) instead of just Enter, as I stated last night. It works.

    - Moo
    Attached Files Attached Files

  8. #8
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Sum Product is showing 0 when i use f9 to evaluate, it shows 1.

    The OPs formula also works if array entered

  9. #9
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,845

    Re: Sum Product is showing 0 when i use f9 to evaluate, it shows 1.

    Yes, it does. I've used SUMPRODUCT sparingly in the past, and have always used it as an alternative to ARRAY formulas, so I didn't even think to try it, but as my attached file shows, it does.

  10. #10
    Registered User
    Join Date
    04-15-2015
    Location
    Oklahoma
    MS-Off Ver
    365
    Posts
    4

    Re: Sum Product is showing 0 when i use f9 to evaluate, it shows 1.

    Thanks for the help guys. I was not using CTRL+SHIFT+ENTER even after Moo the Dog told me to.

  11. #11
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Sum Product is showing 0 when i use f9 to evaluate, it shows 1.

    Glad to help & thanks for the feedback

  12. #12
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,845

    Re: Sum Product is showing 0 when i use f9 to evaluate, it shows 1.

    No problem, jeeke. Glad to help, and thanks for the feedback! Have a great day!

    - Moo

+ 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] Array formula returns zero, but evaluate formula shows a value
    By GriffinCarpenter in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 01-27-2015, 07:53 AM
  2. Formula should evaluate to zero; showing scientific notation instead
    By yay_excel in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-28-2013, 09:34 PM
  3. [SOLVED] Function Arguments window shows result, cell shows a 0
    By fluffsmckenzie in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-09-2013, 05:48 PM
  4. Replies: 3
    Last Post: 11-08-2010, 10:26 PM
  5. Product Report Showing Ranges and Missing Values
    By woodsonline in forum Excel General
    Replies: 1
    Last Post: 09-16-2010, 10:44 AM
  6. Sum Product Shows Zero
    By john_mc in forum Excel General
    Replies: 2
    Last Post: 09-01-2006, 03:26 AM
  7. Replies: 1
    Last Post: 10-18-2005, 01:05 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1