Closed Thread
Results 1 to 17 of 17

SUMIF for a specific month?

  1. #1
    Registered User
    Join Date
    03-31-2009
    Location
    Leicester, England
    MS-Off Ver
    Excel 2007
    Posts
    32

    SUMIF for a specific month?

    i thought i had something sorted out for this yesterday, but coming back to it today it hasn't worked at all.

    i've created a spreadsheet with records of what jobs my heavy plant repair company has opened, which contains such data as when the job was if and when the job was closed and the value of the job when it was closed.

    now i have to create a summary sheet that totals the values of the closed jobs in a given month. i've included an mock up of the database in question, but i just can't seem to get it to work.

    can anyone help?
    Attached Files Attached Files
    Last edited by admiraldick; 05-13-2009 at 08:03 AM.

  2. #2
    Valued Forum Contributor Macdave_19's Avatar
    Join Date
    03-14-2007
    Location
    Birmingham, England
    MS-Off Ver
    12.0
    Posts
    808

    Re: SUMIF for a specific month?

    add a column called month and use the formula =MONTH then use this column within the sumif.

    i've altered your original file to show you
    Attached Files Attached Files
    Mr MaGoo
    Magoo.Inc MMVII

    If i've helped please add to my Rep by Clicking on the Blue Scales in the top right hand corner of the post

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

    Re: SUMIF for a specific month?

    Or try this without mont column:

    =SUMPRODUCT(--(MONTH($D$2:$D$13)=G3);$E$2:$E$13)

    Where G3:G14 are months 1-12

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

    Re: SUMIF for a specific month?

    If you change your months to be proper dates, ie F3 is entered as 1/1 and so on you can avoid SUMPRODUCT/helpers and utilise 2007 SUMIFS function (& EDATE), eg:

    G3: =SUMIFS(D:D,C:C,">="&F3,C:C,"<"&EDATE(F3,1))
    copied down

    (SUMIFS is not available prior to XL2007 and EDATE would require Analysis ToolPak Activation in earlier versions)

  5. #5
    Forum Contributor mubashir aziz's Avatar
    Join Date
    03-18-2009
    Location
    Lahore, Pakistan
    MS-Off Ver
    MS Office 2013
    Posts
    533

    Re: SUMIF for a specific month?

    Changes are shaded in yellow colors ......

  6. #6
    Registered User
    Join Date
    03-31-2009
    Location
    Leicester, England
    MS-Off Ver
    Excel 2007
    Posts
    32

    Re: SUMIF for a specific month?

    thanks guys for your help. they all seem like pretty good answers, but there is a further complication that seems to be botching things up. the summary is in a different Workbook to the table of information.

    all of the examples you've given me seem to work fine on the same sheet, but when i change the references to being in seperate books it won't work. is there a way around this?

  7. #7
    Forum Contributor mubashir aziz's Avatar
    Join Date
    03-18-2009
    Location
    Lahore, Pakistan
    MS-Off Ver
    MS Office 2013
    Posts
    533

    Re: SUMIF for a specific month?

    May be you are entering wrong formula's and entereing incorrect reference number just check it again carefully .......
    If this post helps, Please don't 4get to click the star icon located at the bottom left of my Post.

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

    Re: SUMIF for a specific month?

    A SUMPRODUCT approach should work without incident with an external source even if the other file is closed... SUMIF will not.

  9. #9
    Registered User
    Join Date
    03-31-2009
    Location
    Leicester, England
    MS-Off Ver
    Excel 2007
    Posts
    32

    Re: SUMIF for a specific month?

    i have checked and double checked that the formula is the same, is refering to the correct cell addresses and so on.

    @ mubashir aziz: the formulas you use in your example produce a #VALUE! result when i try and use addresses from another workbook.

  10. #10
    Forum Contributor mubashir aziz's Avatar
    Join Date
    03-18-2009
    Location
    Lahore, Pakistan
    MS-Off Ver
    MS Office 2013
    Posts
    533

    Re: SUMIF for a specific month?

    Did you change the format in F column. Just check that in my file in column F i've changed you text format of Jan , Feb in date format 1-1-09 and then i applied date format on it ..... in your original file if you will not change your text format in date format then it will give error of # value .... Just check and confirm me again .... Just check yellow shaded column in my file ......

  11. #11
    Registered User
    Join Date
    03-31-2009
    Location
    Leicester, England
    MS-Off Ver
    Excel 2007
    Posts
    32

    Re: SUMIF for a specific month?

    i have checked and double checked the format, the dates are most definitely stored and displayed correctly. also, i thought i might have more success if they were in the same workbook but on different sheets, but i'm still getting the same #VALUE! error.

    using your own example spreadsheet i've split the raw data and the summary between two sheets in the same workbook, but as you can see, its only producing the error message.
    Attached Files Attached Files

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

    Re: SUMIF for a specific month?

    You're referencing the header row in your Sheet2 ranges, given row 1 is not a date MONTH(header) will generate a #VALUE! error therefore you need to exclude row 1 in both C & D ranges .. though D1 will not generate an error using the double unary approach the ranges used must be of identical dimensions so by altering C dimensions you are obliged to alter D dimensions similarly.

    Also although in 2007 you can use entire column references in Sumproduct (you can't in earlier versions) you should avoid doing so - keep ranges to a minimum else performance will be adversely affected.

    On a final note - if you opt to house your data within one file you should revert to SUMIFS given you're using XL2007 - it will be more efficient than using SUMPRODUCT... SUMPRODUCT is the better option if your source data is to be housed in an external file which may at times be closed.

  13. #13
    Forum Contributor mubashir aziz's Avatar
    Join Date
    03-18-2009
    Location
    Lahore, Pakistan
    MS-Off Ver
    MS Office 2013
    Posts
    533

    Re: SUMIF for a specific month?

    Try attached sheet in which
    I've created two dynamic ranges "MonthClosed" & " ValueClosed". Now it will auto populated as you will enter dates and values

    =SUMPRODUCT(--(MONTH(MonthClosed)=MONTH(B5)),ValueClosed)
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    03-31-2009
    Location
    Leicester, England
    MS-Off Ver
    Excel 2007
    Posts
    32

    Re: SUMIF for a specific month?

    fantastic, that has it sorted. thanks very much, you guys have been a truly massive help. it now works perfectly between two workbooks.

    just for the sake of detail: i used the dynamic ranges, as you suggested mubashir aziz, but was initially having some difficulty because it wasn't including the whole range of records. i realised this was because i was counting the number of jobs closed/invoiced, rather than the total number of records. so i easily switched the COUNT function to focus on the jobs unique identifier rather than the final cost and it worked out fine.

    thanks again guys.

  15. #15
    Registered User
    Join Date
    02-15-2014
    Location
    Iasi, Romania
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: SUMIF for a specific month?

    Quote Originally Posted by mubashir aziz View Post
    Changes are shaded in yellow colors ......
    That works for me THANk s and is cool because it can be used in any language U are the man

  16. #16
    Forum Contributor
    Join Date
    01-30-2011
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2010
    Posts
    604

    Re: SUMIF for a specific month?

    try this...
    Attached Files Attached Files

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

    Re: SUMIF for a specific month?

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

Closed 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