+ Reply to Thread
Results 1 to 8 of 8

sum, if, month functions, help pls!

  1. #1
    Registered User
    Join Date
    03-25-2008
    Posts
    24

    sum, if, month functions, help pls!

    how can i combine a sum function with if functions with many dates?

    example:
    =SUM(IF(MONTH(I30)=MONTH(N30),R40,0),IF(MONTH(I30)=MONTH(N31),R41,0))

    month
    Jan-09 (I30)

    dates
    1/3/09 (N30)
    1/10/09 (N31)

    values
    5 (R40)
    5 (R41)

    if i have many months and many dates, can i change the function to =sum(if(month(I30)=month(N30:N31),R40:R41,0))

    i think it could work in excel 2003 somehow but it can't work in excel 07, help anyone?

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: sum, if, month functions, help pls!

    Try SUMIF formula SUMIF(range, criteria, [sum_range])

  3. #3
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: sum, if, month functions, help pls!

    If you're using XL2007 and have multiple conditions (ie > and < ) see SUMIFS/COUNTIFS function in XL Help.

    Perhaps also consider a Pivot Table.

  4. #4
    Registered User
    Join Date
    03-16-2009
    Location
    Turku, Finland
    MS-Off Ver
    Excel 2003, 2007
    Posts
    8

    Re: sum, if, month functions, help pls!

    Quote Originally Posted by rukia View Post

    if i have many months and many dates, can i change the function to =sum(if(month(I30)=month(N30:N31),R40:R41,0))

    i think it could work in excel 2003 somehow but it can't work in excel 07, help anyone?
    I tried the formula you suggested and it works for me in xl2007.
    Remember to confirm it with ctrl+shift+enter as it is an arrayformula.

  5. #5
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: sum, if, month functions, help pls!

    This is not an advised approach.

    In XL2007 use:

    =SUMIFS(R40:R41,N30:N31,">="&I30,N30:N31,"<"&EDATE(I30,1))

    In earlier versions still more efficient to use 2 SUMIFs rather than SUMPRODUCT/Array

    =SUMIF(N30:N31,">="&I30,R40:R41)-SUMIF(N30:N31,">"&EDATE(I30,1),R40:R41)

    (to use EDATE pre-XL2007 you must activate Analysis ToolPak Add-In)

  6. #6
    Registered User
    Join Date
    03-25-2008
    Posts
    24

    Re: sum, if, month functions, help pls!

    Quote Originally Posted by snowblizz View Post
    I tried the formula you suggested and it works for me in xl2007.
    Remember to confirm it with ctrl+shift+enter as it is an arrayformula.
    YESS! thanks alot! that definitely helped!

  7. #7
    Registered User
    Join Date
    03-25-2008
    Posts
    24

    Re: sum, if, month functions, help pls!

    Quote Originally Posted by DonkeyOte View Post
    This is not an advised approach.

    In XL2007 use:

    =SUMIFS(R40:R41,N30:N31,">="&I30,N30:N31,"<"&EDATE(I30,1))

    In earlier versions still more efficient to use 2 SUMIFs rather than SUMPRODUCT/Array

    =SUMIF(N30:N31,">="&I30,R40:R41)-SUMIF(N30:N31,">"&EDATE(I30,1),R40:R41)

    (to use EDATE pre-XL2007 you must activate Analysis ToolPak Add-In)
    thank you for helping me! much appreciated! =)

  8. #8
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: sum, if, month functions, help pls!

    Don't forget to make thread [solved]

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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