+ Reply to Thread
Results 1 to 6 of 6

Sumproduct #Value! error on monthly totals

  1. #1
    Registered User
    Join Date
    09-08-2019
    Location
    East Sussex, England
    MS-Off Ver
    16.8 Excel for Mac
    Posts
    6

    Sumproduct #Value! error on monthly totals

    Hi,
    I'm a novice excel user having a problem in obtains monthly totals, using a Sumproduct array, as I keep getting a #Value! error.
    The CSE code is:
    {=SUMPRODUCT("(MONTH($Master!$A$3:$A$54)=7)*(YEAR($Master!$A$3:$A$54)=2019)*Master!$E$3:$E$54)")}

    Where columns in a Master sheet are:-
    A=Date
    (entered randomly using either (/) or (.) separators and with d or dd or m or mm and yy or yyyy but formatted by using SHORT DATE to display uniformly, eg: 31/12/2019

    B=PurchaserID
    C=RefID
    D=SupplierID
    E=Item amount

    The code is in a cell on a separate sheet.

    Can anyone help?
    Cheers, H.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,074

    Re: Sumproduct #Value! error on monthly totals

    Try without the quotes
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Sumproduct #Value! error on monthly totals

    Hi and welcome to the forum.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    but arguably ever since the SUMIFS() function was introduced it's perhaps simpler to use that. i.e.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  4. #4
    Registered User
    Join Date
    09-08-2019
    Location
    East Sussex, England
    MS-Off Ver
    16.8 Excel for Mac
    Posts
    6

    Re: Sumproduct #Value! error on monthly totals

    Hi Trevor,
    Thanks for your help. The code you provided did not work at first, but I noticed that the first instance of $Master was highlighted in error notification. I deleted the $ symbol and the next error code highlife the next occurrence, so deleted that as well. The 3rd error showed a spelling error and offered a correction, which I accepted (though I couldn't see where the mistake was - but....it worked!

    The final code, before CSE, is:-
    =SUMPRODUCT((MONTH(Master!$A$3:$A$54)=7)*(YEAR(Master!$A$3:$A$54)=2019)*Master!$E$3:$E$54)

    Thank you for all your help.
    Kind regards, H.

  5. #5
    Registered User
    Join Date
    09-08-2019
    Location
    East Sussex, England
    MS-Off Ver
    16.8 Excel for Mac
    Posts
    6

    Re: Sumproduct #Value! error on monthly totals

    Hi Richard,
    Thank you for your help. Both codes worked first time!
    Not quite sure how they work as my poor ancient brain is tired and frazzled at this time of night, but will take a fresh look tomorrow.
    Many thanks and kind regards,
    H.

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,074

    Re: Sumproduct #Value! error on monthly totals

    You're welcome.



    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

+ 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. Monthly totals
    By kermit7 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-23-2017, 12:02 PM
  2. [SOLVED] Monthly Totals
    By thekiddzac in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 08-05-2015, 03:57 PM
  3. [SOLVED] How do I group daily totals into weekly/monthly/quarterly totals
    By situationroom in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-28-2013, 09:58 AM
  4. Monthly Totals
    By ZackG in forum Excel General
    Replies: 2
    Last Post: 02-22-2011, 04:07 PM
  5. [SOLVED] Summing Weekly Totals into Monthly Totals
    By steph44haf in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-05-2006, 11:55 AM
  6. [SOLVED] Monthly Totals
    By Jasmine in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-31-2005, 02:05 PM
  7. [SOLVED] How do I sum YTD totals based on monthly totals
    By Bsgrad02 in forum Excel General
    Replies: 3
    Last Post: 07-12-2005, 12:05 PM

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