+ Reply to Thread
Results 1 to 2 of 2

Multi Conditional Sum

  1. #1
    Registered User
    Join Date
    08-27-2010
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    2

    Multi Conditional Sum

    Hi everyone. I'm looking for some help writing a formula that will sum a column if a particular product is listed in column A and a date time stamp is no more than three months old.

    For example

    Product1 6/1/10 6 would not be summed
    Product1 9/2/10 5 would be summed.
    Sum = 5

    Here is what I have so far. However, it gives me a 0 and it should be 12.
    =SUM((Sheet1!A2:A99="BigRewards")*(Sheet1!J2:J999>Sheet1!L2)*(Sheet1!G2:G999))

    *J2-J999 is where the date time stamp is stored.
    * L2 is a cell with today() minus three months or =EDATE(TODAY(),-3)
    * G2:G999 are the cells I want to sum.
    * I am using Excel 2007.

    Thank you
    `Brian

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Multi Conditional Sum

    Hello Brian,

    That formula should work.....but it's an "array formula". To make it work correctly you need to confirm with CTRL+SHIFT+ENTER so that curly braces like { and } appear around the formula

    ....but you can avoid all that if you use SUMIFS function available in Excel 2007, i.e.

    =SUMIFS(Sheet1!G2:G999,Sheet1!A2:A99,"BigRewards",Sheet1!J2:J999,">"&Sheet1!L2)

    Notice that the range to be summed is at the start

    If you want you can cut out cell L2 and use EDATE directly in the formula, i.e.

    =SUMIFS(Sheet1!G2:G999,Sheet1!A2:A99,"BigRewards",Sheet1!J2:J999,">"&EDATE(TODAY(),-3))
    Audere est facere

+ 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