Results 1 to 5 of 5
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Sumif With 2 Conditions (2000)

    HELP!
    I would like to sum a range of cells in, say, Column A if 2 conditions are met: first, if the cell contents of each respective cell in Column B is a certain letter, say an "o", andsecond, if the date in each respective cell in Column C is, say, prior to July 1, 2003.
    Any experts out there?
    Thanks,
    Jeff

  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

    Re: Sumif With 2 Conditions (2000)

    Something like this array should workconfirm with ctrl-shift-enter)

    <pre>=SUM(IF((B1:B100="o")*(C1:C100<DATE(2003,7,1) ),A1:A100))</pre>


    change the range as appropriate.

    Note: you can replace the SUM with any of the stat functions, min, max, average, count, var, stddev, etc if you want other info.

    For more on array formulas see chip pearson's site:
    http://www.cpearson.com/excel/array.htm
    Steve

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sumif With 2 Conditions (2000)

    If your values are in A1:C20, then the following formula should do what you asked:

    <pre>=SUM(IF((NOT(ISERROR(FIND("o",B1:B20))))*(C1: C20<DATE(2003,7,1)),A1:A20))
    </pre>


    This is an array formula which means you must press Ctrl+Shift+Enter to enter the formula in the cell.
    Legare Coleman

  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

    Re: Sumif With 2 Conditions (2000)

    This is good if the first criteria was to CONTAIN an "o", but I read his request:
    <hr>if the cell contents of each respective cell in Column B is a certain letter, say an "o"<hr>
    to mean that the cells had to ="o" NOT just contain an "o".

    Steve

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sumif With 2 Conditions (2000)

    I read it to mean that the cell contained the letter o somewhere in a string. I think it could mean either.
    Legare Coleman

Posting Permissions

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