Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Jan 2004
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sum (Excell 2000)

    Hallo

    I have a small problem which I can not seem to solve.

    The problem is as follows:
    I have dates in one column and figures in the next. I also have a starting and End dates in two cells. I want to sum the total from one date to the next by just changing the Start or End date.

    Has this problem been solved before? Is so please direct me, I can not find anything like that unless I do not search correctly.

    Any help will be appreciated.

    I attached a spread sheet as sample.

    Thank you

    Kobus
    Regards
    Kobus

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sum (Excell 2000)

    There are several possibilities:

    =SUMPRODUCT((D920)*(B9:B20>=B4)*(B9:B20<=B5))

    =SUMPRODUCT(D920,N(B9:B20>=B4),N(B9:B20<=B5))

    =SUM((D920)*(B9:B20>=B4)*(B9:B20<=B5))

    =SUM(IF((B9:B20>=B4)*(B9:B20<=B5),D920))

    The first two formulas are standard formulas; the last two are array formulas - confirm them with Ctrl+Shift+Enter instead of just Enter.

  3. #3
    Star Lounger
    Join Date
    Jan 2004
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sum (Excell 2000)

    Hans

    As always, you show your absolute mastery over our normal mortals.

    Thank you very much.

    It works perfect.

    Kobus
    Regards
    Kobus

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sum (Excell 2000)

    And of course:

    =SUMIF(B9:B20,">" & B5,D920)-SUMIF(B9:B20,"<" & B4,D920)

    I might have missed an =< or an =>, but you get the idea.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sum (Excell 2000)

    Thanks, I always forget that one.

Posting Permissions

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