sumif or sumproduct based on current month and year
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: sumif or sumproduct based on current month and year

  1. #1
    New Member
    Join Date
    Apr 2015
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default sumif or sumproduct based on current month and year

    2 columns, one date format xx/xx/xxxx and the other accounting format

    I'm looking to sum the value of the accounting column based on the current month and year.

    Preferably without having to add additional columns.

    I was following a previously used SUMPRODUCT formula but it kept giving me VALUE! error

    =SUMPRODUCT(--(MONTH(V:$V)=8),--(YEAR(V:$V)=2019),AI:$AI)

    The formula is pulling the data from a different workbook, so I have editted the columns to simplify for this, although I'm wondering if that's causing the issue, not because the source is incorrect as it's auto-formulated but because it's sourced from a dropbox folder on my pc

    Any help appreciated

  2. #2
    Board Regular Aryatect's Avatar
    Join Date
    Jun 2019
    Location
    Chicago, USA
    Posts
    308
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: sumif or sumproduct based on current month and year

    Hi Try below:

    ABD
    1DateValueSum
    28/1/2019$1,226.00$46,945.00
    38/2/2019$1,840.00
    48/3/2019$1,332.00
    58/4/2019$1,649.00
    68/5/2019$1,876.00
    78/6/2019$1,298.00
    88/7/2019$1,993.00
    98/8/2019$1,737.00
    108/9/2019$1,122.00
    118/10/2019$1,556.00
    128/11/2019$1,092.00
    138/12/2019$1,953.00
    14$43,690.00$1,898.00
    158/14/2019$1,630.00
    168/15/2019$1,257.00
    178/16/2019$1,580.00
    188/17/2019$1,060.00
    198/18/2019$1,262.00
    208/19/2019$1,472.00
    218/20/2019$1,606.00
    228/21/2019$1,936.00
    238/22/2019$1,093.00
    248/23/2019$1,274.00
    258/24/2019$1,834.00
    268/25/2019$1,895.00
    278/26/2019$1,408.00
    288/27/2019$1,951.00
    298/28/2019$1,106.00
    308/29/2019$1,273.00
    318/30/2019$1,174.00
    328/31/2019$1,562.00
    339/1/2019$1,811.00
    349/2/2019$1,197.00
    359/3/2019$1,842.00
    369/4/2019$1,062.00
    379/5/2019$1,884.00
    389/6/2019$1,766.00
    399/7/2019$1,428.00
    409/8/2019$1,527.00

    Sheet4



    Worksheet Formulas
    CellFormula
    D2=SUMPRODUCT((TEXT(A2:A40,"myyyy")=TEXT(TODAY(),"myyyy"))*(B2:B40))

    Thanks!

    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  3. #3
    Board Regular Aryatect's Avatar
    Join Date
    Jun 2019
    Location
    Chicago, USA
    Posts
    308
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: sumif or sumproduct based on current month and year

    it should work across the workbooks even if the workbook where the numbers are stored is closed.
    Thanks!

    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  4. #4
    New Member
    Join Date
    Apr 2015
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: sumif or sumproduct based on current month and year

    Quote Originally Posted by Aryatect View Post
    Hi Try below:

    A B D
    1 Date Value Sum
    2 8/1/2019 $1,226.00 $46,945.00
    3 8/2/2019 $1,840.00
    4 8/3/2019 $1,332.00
    5 8/4/2019 $1,649.00
    6 8/5/2019 $1,876.00
    7 8/6/2019 $1,298.00
    8 8/7/2019 $1,993.00
    9 8/8/2019 $1,737.00
    10 8/9/2019 $1,122.00
    11 8/10/2019 $1,556.00
    12 8/11/2019 $1,092.00
    13 8/12/2019 $1,953.00
    14 $43,690.00 $1,898.00
    15 8/14/2019 $1,630.00
    16 8/15/2019 $1,257.00
    17 8/16/2019 $1,580.00
    18 8/17/2019 $1,060.00
    19 8/18/2019 $1,262.00
    20 8/19/2019 $1,472.00
    21 8/20/2019 $1,606.00
    22 8/21/2019 $1,936.00
    23 8/22/2019 $1,093.00
    24 8/23/2019 $1,274.00
    25 8/24/2019 $1,834.00
    26 8/25/2019 $1,895.00
    27 8/26/2019 $1,408.00
    28 8/27/2019 $1,951.00
    29 8/28/2019 $1,106.00
    30 8/29/2019 $1,273.00
    31 8/30/2019 $1,174.00
    32 8/31/2019 $1,562.00
    33 9/1/2019 $1,811.00
    34 9/2/2019 $1,197.00
    35 9/3/2019 $1,842.00
    36 9/4/2019 $1,062.00
    37 9/5/2019 $1,884.00
    38 9/6/2019 $1,766.00
    39 9/7/2019 $1,428.00
    40 9/8/2019 $1,527.00
    Sheet4

    Worksheet Formulas
    Cell Formula
    D2 =SUMPRODUCT((TEXT(A2:A40,"myyyy")=TEXT(TODAY(),"myyyy"))*(B2:B40))
    Still getting VALUE! error. If some cells in either column were blank could this cause the issue?

  5. #5
    Board Regular Aryatect's Avatar
    Join Date
    Jun 2019
    Location
    Chicago, USA
    Posts
    308
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: sumif or sumproduct based on current month and year

    If there is any text there then it can create this error. You can use sumifs, but it does not work if the original file is closed.

    SUMIFS option:
    Code:
    =SUMIFS($B$2:$B$40,$A$2:$A$40,">="&EOMONTH(TODAY(),-1)+1,$A$2:$A$40,"<="&EOMONTH(TODAY(),0))
    Another option can be:

    Code:
    =SUM((TEXT(A2:A40,"mmyyyy")=TEXT(TODAY(),"mmyyyy"))*IF(ISNUMBER($B$2:$B$40),$B$2:$B$40,0))
    Confirm with Ctrl+Shift+Enter not just enter
    Thanks!

    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,113
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: sumif or sumproduct based on current month and year

    Control+shift+enter, not just enter:

    =
    SUM(IF(ISNUMBER(V:$V),IF((MONTH(V:$V)=8)*(YEAR(V:$V)=2019),AI:$AI)))
    Assuming too much and qualifying too much are two faces of the same problem.

  7. #7
    New Member
    Join Date
    Apr 2015
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: sumif or sumproduct based on current month and year

    Quote Originally Posted by Aryatect View Post
    If there is any text there then it can create this error. You can use sumifs, but it does not work if the original file is closed.

    SUMIFS option:
    Code:
    =SUMIFS($B$2:$B$40,$A$2:$A$40,">="&EOMONTH(TODAY(),-1)+1,$A$2:$A$40,"<="&EOMONTH(TODAY(),0))
    Another option can be:

    Code:
    =SUM((TEXT(A2:A40,"mmyyyy")=TEXT(TODAY(),"mmyyyy"))*IF(ISNUMBER($B$2:$B$40),$B$2:$B$40,0))
    Confirm with Ctrl+Shift+Enter not just enter

    Quote Originally Posted by Aladin Akyurek View Post
    Control+shift+enter, not just enter:

    =
    SUM(IF(ISNUMBER(V:$V),IF((MONTH(V:$V)=8)*(YEAR(V:$V)=2019),AI:$AI)))
    Wanted to thank you both. The latter options worked perfectly using ctrl+shift+enter.

    So that I'm learning, why the use of crtl+shift+enter? If the formula is repeated in the same sheet, different cell, is the same process applicable? I have a running monthly section, so it's the same each month, obviously I adjust the month to suit and confirm entry using crtl+shift+enter each time?

    I'm specifically using Aladin's formula, I'm assuming that whether the source file is open or not won't matter, as only applicable to SUMIF right?

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,113
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: sumif or sumproduct based on current month and year

    Quote Originally Posted by Antispam246 View Post
    Wanted to thank you both. The latter options worked perfectly using ctrl+shift+enter.

    So that I'm learning, why the use of crtl+shift+enter? If the formula is repeated in the same sheet, different cell, is the same process applicable? I have a running monthly section, so it's the same each month, obviously I adjust the month to suit and confirm entry using crtl+shift+enter each time?

    I'm specifically using Aladin's formula, I'm assuming that whether the source file is open or not won't matter, as only applicable to SUMIF right?
    1. Control+shift+enter is an Excel requirement for signaling Excel that the formula in question is an array-processing formula. Excel's calculation treats such formulas differently (qua algorithms it recruits) than the formulas which do nor require array-processing.

    2. You can have the month and year spacifications in cells of their own, say in X2 (8) and Y2 (2019). If you have a different set of specs in X3 and Y3, you can copy down the array formula for X2 and Y2.

    3. Array-prcossing formulas work for both open and closed workbooks alike.
    Assuming too much and qualifying too much are two faces of the same problem.

  9. #9
    New Member
    Join Date
    Apr 2015
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: sumif or sumproduct based on current month and year

    Just a follow up on this, using the same formula is it possible to incorporate another requirement for the outcome, in this case, as well as the date a specific text from another column.

    I've been trying to figure out if I can use the IF(search("xxx xxx") function but unsure if the original formula would have to be re-worked for it to work.

  10. #10
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,113
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: sumif or sumproduct based on current month and year

    Quote Originally Posted by Antispam246 View Post
    Just a follow up on this, using the same formula is it possible to incorporate another requirement for the outcome, in this case, as well as the date a specific text from another column.

    I've been trying to figure out if I can use the IF(search("xxx xxx") function but unsure if the original formula would have to be re-worked for it to work.
    Which column is it and what is the text you are looking for?
    Assuming too much and qualifying too much are two faces of the same problem.

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
  •