Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jan 2002
    Thanked 0 Times in 0 Posts

    IF Formula - Date then amount (2000)

    I am attempting to create an IF (or SUMIF) formula that looks through its range
    1st: for a specified date range, then
    2nd: adds the dollar amounts for any fields that match the criteria
    I can't seem to figure out how to input the criteria for a date range of one month

    For example, I want the formula to search though a list of dates and pick out all of the cells that are, say, from Jan 1 - Jan 31, and then look at the amount that corresponds to that date and add up all of those values into one. This is the end result of the formula - a dollar value.

    Confused? Me too. Any help is greatly appreciated.

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Salt Lake City, Utah, USA
    Thanked 5 Times in 5 Posts

    Re: IF Formula - Date then amount (2000)

    [Edited, munged the syntax]


    If you want to reference the dates rather than hard code:

    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    3 Star Lounger
    Join Date
    Jan 2002
    The Hague, Netherlands
    Thanked 0 Times in 0 Posts

    Re: IF Formula - Date then amount (2000)

    The question involves totaling amounts involving a criterion month.

    Lets say that A2:A100 houses full date entries and B2:B100 dollar amounts.

    In order to use the SUMIF effectively, which is an appropriate function for the task, you need the following:

    In C2 enter: =MONTH(A2) [or =TEXT(A2,"MMM") ]

    Copy down this formula till C100.

    In D2 enter: a month number of interest if column C created with =MONTH(..) or a 3-letter month name if column C is created with =TEXT(...,"MMM"),

    In E2 enter: =SUMIF($C$2:$C$100,D2,$B$2:$B$100)

    Another method, where you don't need to create column C, would be with SUMPRODUCT.

    If D2 is a month number (the criterion month),

    in E2 enter: =SUMPRODUCT((MONTH($A$2:$A$200)=D2)*($B$2:$B$100))

    Or, if D2 is a 3-letter month name (the criterion month),

    in E2 enter: =SUMPRODUCT((TEXT($A$2:$A$200,"MMM")=D2)*($B$2:$B$ 100))

    The first method (with SUMIF) should be preferred above the second if the actual data range consists of more than 1000 rows.
    Microsoft MVP - Excel

Posting Permissions

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