+ Reply to Thread
Results 1 to 9 of 9

Sumproduct formula not giving correct count

  1. #1
    Forum Contributor
    Join Date
    05-18-2012
    Location
    Chicopee, Ma
    MS-Off Ver
    Excel 2010
    Posts
    188

    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

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    17,965

    Re: Sumproduct formula not giving correct count

    File is password protected: please remove p/w protection.

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,792

    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

  4. #4
    Forum Contributor
    Join Date
    05-18-2012
    Location
    Chicopee, Ma
    MS-Off Ver
    Excel 2010
    Posts
    188

    Re: Sumproduct formula not giving correct count

    The password is Sigmet123
    The other formulas seem to be working ox with 2016 and 2017

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,792

    Re: Sumproduct formula not giving correct count

    file opens fine using Read Only

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    17,965

    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.

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,792

    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))

  8. #8
    Forum Contributor
    Join Date
    05-18-2012
    Location
    Chicopee, Ma
    MS-Off Ver
    Excel 2010
    Posts
    188

    Re: Sumproduct formula not giving correct count

    Thank you all for the help.
    I didn't see that error.

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,792

    Re: Sumproduct formula not giving correct count

    Happy to help, John solved it though

+ 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. 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1