Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jan 2010
    Location
    Sacramento, CA
    Posts
    7
    Thanks
    3
    Thanked 1 Time in 1 Post

    Using SUMIFS to sum a column when another column matches year val.

    I'm trying to sum values in column A only when a corresponding date value in column B matches the year 2010. For ex. if a table contains:

    A B
    --------------
    75 03/11/10
    15 05/21/11
    25 09/30/10
    50 08/05/11

    .... the total should be 100.

    I can't seem to get the SUMIF/SUMIFS formula to use the YEAR() function correctly. Is there another way?

    Thanks.

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Could try an array formula (CTRL+Shift+Enter): =SUM((YEAR(B1:B4)=2010)*A1:A4)

  3. The Following User Says Thank You to kweaver For This Useful Post:

    artg (2011-12-29)

  4. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    SUMIF(S) only allows you to pass ranges for the criteria, not calculated values. You could use:
    =SUMIFS(A:A,B:B,">="&Date(2010,1,1),B:B,"<"&date(2 011,1,1))
    Regards,
    Rory

    Microsoft MVP - Excel

  5. The Following User Says Thank You to rory For This Useful Post:

    artg (2011-12-29)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •