Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Dec 2006
    Posts
    250
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formula for excel 2010 to compare dates in cells and return sum

    Hello-I am trying to develop a formula where I would take the dates in a column range and compare it to a date on a particular cell. If the return value is true, I would then want it to sum the numbers in a different column. See sample below.

    column A Column B Column C Column E
    cards 1 3/14/2014 2/1/2014
    ink 0.5 2/28/2014
    envelops 1 2/28/2014
    staples 2 1/31/2014
    toner 0.75 1/1/2014

    I want it to give me the sum of column B where the date is greater than or equal to column e. Thank you.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    How about this:
    Code:
    =SUMPRODUCT((C2:C6>=E2:E6)*B2:B6)
    or if you don't want to sum the blanks in col E:
    Code:
    =SUMPRODUCT((C2:C6>=E2:E6)*B2:B6*(E2:E6<>""))
    Steve
    Last edited by sdckapr; 2014-03-25 at 15:56. Reason: used D not E

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Seba,

    Another option:
    Seba.JPG
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    RG,
    Yours may be the correct solution. My solution assumes a separate date in each row in E to compare to the value in C. With only 1 date in the example, perhaps they were all supposed to be compared to E2.

    If that is the logic another solution is
    =SUMIF(C2:C6,">="&E2,B2:B6)

    so that E2 can remain a date instead of having to enter the text string

    Steve

  5. #5
    3 Star Lounger
    Join Date
    Dec 2006
    Posts
    250
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you all for your help. Sdckapr formula computed what I needed. One more question, if I want to have the formula give me everything that felt 30 days after the date specify in E2, how would I do that?

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    How about:
    =SUMIF(C2:C6,">="&E2+30,B2:B6)

    Steve

Posting Permissions

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