+ Reply to Thread
Results 1 to 6 of 6

Sumif between a range of dates plus one additional criteria

  1. #1
    Forum Contributor
    Join Date
    08-03-2005
    Posts
    269

    Sumif between a range of dates plus one additional criteria

    Hi, We are building our own version of quicken in excel and are almost finished. I need to sumif based on 2 criteria where the first criteria is a range of dates and the second is a category like groceries. So what I need to do is look at a date column and pick out only the dates that fall in a certain range, then I need to look into a category column and find "groceries" that have the dates I am looking for, then go add the amounts of those groceries. I have made this little file as an example. I am just stumped on this one.
    Attached Files Attached Files
    Last edited by caliskier; 01-09-2011 at 10:27 PM.

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Sumif between a range of dates plus one additional criteria

    Like so:

    =SUMPRODUCT(--($B$2:$B$16>=$G$2), --($B$2:$B$16<=$G$3), --($C$2:$C$16=$F5), $D$2:$D$16)


    See the example workbook to see how it works with F5 as a reference, too.

    NOTE: Don't try to replace the set ranges with whole column references. You can expand the range, but this array formula has a lot of overhead, so only expand it as far as needed, plus a little more, perhaps. Don't go nuts.
    Attached Files Attached Files
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Forum Contributor
    Join Date
    08-03-2005
    Posts
    269

    Re: Sumif between a range of dates plus one additional criteria

    Quote Originally Posted by JBeaucaire View Post
    Like so:

    =SUMPRODUCT(--($B$2:$B$16>=$G$2), --($B$2:$B$16<=$G$3), --($C$2:$C$16=$F5), $D$2:$D$16)


    See the example workbook to see how it works with F5 as a reference, too.

    NOTE: Don't try to replace the set ranges with whole column references. You can expand the range, but this array formula has a lot of overhead, so only expand it as far as needed, plus a little more, perhaps. Don't go nuts.
    This is very very very nice, thanks for your help. I am not going to go too nuts with this, perhaps I will create a new file everyyear, but this formula will be repeated probably as much as 1440 times if I do what I plan, thats one year. THANKS!!!

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Sumif between a range of dates plus one additional criteria

    Glad it helps.

    If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

  5. #5
    Registered User
    Join Date
    01-28-2014
    Location
    Illinois, USA
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: Sumif between a range of dates plus one additional criteria

    Quote Originally Posted by JBeaucaire View Post
    Like so:

    =SUMPRODUCT(--($B$2:$B$16>=$G$2), --($B$2:$B$16<=$G$3), --($C$2:$C$16=$F5), $D$2:$D$16)


    See the example workbook to see how it works with F5 as a reference, too.

    NOTE: Don't try to replace the set ranges with whole column references. You can expand the range, but this array formula has a lot of overhead, so only expand it as far as needed, plus a little more, perhaps. Don't go nuts.

    I love you. Not really but this was extremely helpful! What do the double dashes actually do?

  6. #6
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Sumif between a range of dates plus one additional criteria

    Turns a bunch of TRUE/FALSE answers in an array of answers into 1s and 0s.

    If you want to see it really work, adjust your formula down to only 5 or 6 rows, the use the Formulas > Evaluate Formula to watch it unfold one step at at time. Very educational.

+ 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