Results 1 to 7 of 7

SUMIF month criteria, how?

This is a discussion on SUMIF month criteria, how? within the Excel Questions forums, part of the Question Forums category; Hi, using SUMIF, the comparison column contains dates in mm/dd/yyyy format. I want to use the month from that column ...

  1. #1
    Board Regular
    Join Date
    Jan 2005
    Posts
    138

    Default SUMIF month criteria, how?

    Hi,
    using SUMIF, the comparison column contains dates in mm/dd/yyyy format.
    I want to use the month from that column as the SUMIF criteria.

    I tinkered with the MONTH() function to no avail.

    Any ideas how to do it?

    Thanks!

  2. #2
    Board Regular
    Join Date
    May 2008
    Location
    Netherlands
    Posts
    692

    Default Re: SUMIF month criteria, how?

    Try
    Code:
    =SUMPRODUCT(IF(MONTH(A1:A10)=1,B1:B10))
    Enter code with Ctrl-Shift-Enter.

    A1:A10 = dates
    B1:B10 = summable numbers
    1 = month
    Regards,
    Stefan


    Using Office 2010

  3. #3
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    19,835

    Default Re: SUMIF month criteria, how?

    Quote Originally Posted by dafan View Post
    =SUMPRODUCT(IF(MONTH(A1:A10)=1,B1:B10))
    You can dispense with the IF, then you don't need CSE, i.e.

    =SUMPRODUCT(--(MONTH(A1:A10)=1),B1:B10)

    Note: Empty cells are deemed to be a January date so you might also want to check that A1:A10 is a date, i.e.

    =SUMPRODUCT(ISNUMBER(A1:A10)*(MONTH(A1:A10)=1),B1:B10)

    ...or add a year check

    =SUMPRODUCT((YEAR(A1:A10)=2008)*(MONTH(A1:A10)=1),B1:B10)

  4. #4
    Board Regular
    Join Date
    Jan 2005
    Posts
    138

    Default Re: SUMIF month criteria, how?

    Thanks dafan.
    What do you mean by enter code with ctrl-shift-enter? Don't I just enter this as a formula?

  5. #5
    Board Regular
    Join Date
    May 2008
    Location
    Netherlands
    Posts
    692

    Default Re: SUMIF month criteria, how?

    With array formulas you need to Ctrl-Shift-Enter. check out the article on CSE-formulas here: http://www.mrexcel.com/tip011.shtml

    Barry: What does -- do here? Im not really familiar with SUMPRODUCT but I thought this would be a good situation to use it.
    Regards,
    Stefan


    Using Office 2010

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    64,415

    Default Re: SUMIF month criteria, how?

    Some options...

    [1] Build a pivot table, grouping dates by months.

    Lets the comparison column be A and the column to sum B (Why not give them self right away?)...

    [2]

    =SUMIF(A:A,">="&E2,B:B)-SUMIF(A:A,">="&F2,B:B)

    where E2 is the month of interest specified as: 2008-01-01 and F2 as 2008-02-01.

    [3]

    Control+shift+enter, not just enter...
    Code:
    =SUM(
       IF(ISNUMBER($A$2:$A$500),
       IF($A$2:$A$500-DAY($A$2:$A$500)+1=E2,
         $B$2:$B$500)))
    Quote Originally Posted by dwest100 View Post
    Hi,
    using SUMIF, the comparison column contains dates in mm/dd/yyyy format.
    I want to use the month from that column as the SUMIF criteria.

    I tinkered with the MONTH() function to no avail.

    Any ideas how to do it?

    Thanks!
    Last edited by Aladin Akyurek; May 17th, 2008 at 09:42 AM. Reason: Typo: 50 --> 500

  7. #7
    Board Regular
    Join Date
    Jan 2005
    Posts
    138

    Default Re: SUMIF month criteria, how?

    Thanks to all for the education!

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
  •  


DMCA.com