+ Reply to Thread
Results 1 to 9 of 9

Sumproduct formula not giving correct count

  1. 03-03-2017, 12:08 PM #1
    mmccra2858
    mmccra2858 is offline
    Forum Contributor
    Join Date
    05-18-2012
    Location
    Chicopee, Ma
    MS-Off Ver
    Excel for Microsoft 365
    Posts
    196

    Sumproduct formula not giving correct count

    Having problems with the formula in Feb Stats D,12 and D,32
    If you filter for Dept 5815 in Archive you will see there are 2 line items in archive, so the count in D,12 should be one for the month of February and the count in D,32 should be a count of two for the year.
    Is there something wrong with the formula?
    Why is it recognizing the item from November but not recognizing the item from February?
    I’m not having a problem with the count for any of the other departments.

    Thanks in advance for your help.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 03-03-2017, 12:19 PM #2
    JohnTopley
    JohnTopley is offline
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,152

    Re: Sumproduct formula not giving correct count

    File is password protected: please remove p/w protection.
    Reply With QuoteRegister To Reply
  3. 03-03-2017, 12:27 PM #3
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,926

    Re: Sumproduct formula not giving correct count

    Does it matter that 1 entry is 2016 and the other is 2017?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  4. 03-03-2017, 12:32 PM #4
    mmccra2858
    mmccra2858 is offline
    Forum Contributor
    Join Date
    05-18-2012
    Location
    Chicopee, Ma
    MS-Off Ver
    Excel for Microsoft 365
    Posts
    196

    Re: Sumproduct formula not giving correct count

    The password is Sigmet123
    The other formulas seem to be working ox with 2016 and 2017
    Reply With QuoteRegister To Reply
  5. 03-03-2017, 12:40 PM #5
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,926

    Re: Sumproduct formula not giving correct count

    file opens fine using Read Only
    Reply With QuoteRegister To Reply
  6. 03-03-2017, 12:44 PM #6
    JohnTopley
    JohnTopley is offline
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,152

    Re: Sumproduct formula not giving correct count

    The formula fails because you have 5815 NUMERIC in "Archive" for February data but are testing against "5815" (Text) in the formula,

    The Value in Archive is "odd" in that other values in A are recorded as TEXT.
    Reply With QuoteRegister To Reply
  7. 03-03-2017, 12:55 PM #7
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,926

    Re: Sumproduct formula not giving correct count

    Good catch John, I saw A1269 had =IF(B1269>0,5815,"") but did not notice A890 had =IF(B890>0,"5815","")

    Looks like that was the only 1 though

    On a side note, you could change that SP to this, so you dont need to hard code the Dept...
    =SUMPRODUCT((Archive!$K$2:$K$5013="P/S")*(Archive!$A$2:$A$5013=MID($A32,6,10))*(MONTH(Archive!$F$2:$F$5013)>=1))
    Reply With QuoteRegister To Reply
  8. 03-03-2017, 01:55 PM #8
    mmccra2858
    mmccra2858 is offline
    Forum Contributor
    Join Date
    05-18-2012
    Location
    Chicopee, Ma
    MS-Off Ver
    Excel for Microsoft 365
    Posts
    196

    Re: Sumproduct formula not giving correct count

    Thank you all for the help.
    I didn't see that error.
    Reply With QuoteRegister To Reply
  9. 03-03-2017, 01:59 PM #9
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,926

    Re: Sumproduct formula not giving correct count

    Happy to help, John solved it though
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Formula is not giving correct answer
    By jackf-nc in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-02-2016, 07:34 PM
  2. The formula is not giving the correct result
    By Patcheen in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-02-2016, 11:44 AM
  3. [SOLVED] Formula not giving correct output
    By aakhan2011 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 03-24-2014, 03:21 AM
  4. [SOLVED] Correct Formula not giving results
    By NJ007 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-25-2014, 09:06 PM
  5. [SOLVED] Why is this formula giving incorrect data (SUMPRODUCT)?
    By Patty McJorst in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 01-24-2014, 02:50 PM
  6. NETWORKDAYS not giving me correct count
    By tinytutu in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-21-2013, 04:10 PM
  7. Sumproduct formula not giving expected results
    By Shocked in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 09-30-2008, 03:12 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1