Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Need help with SUM(IF (Excel 97)

    I need a SUM(IF formula that looks at 2 different sets of criteria in order to display a result. I have seen this formula in other spread sheets, but cannot seem to get it to work in my sheet.

    If the date on Sheet2 matches the date on Sheet1, and the value of Sheet2, Column B = 8, then sum column C. I have seen this displayed like {SUM(IF((rdate=pdate)*(inserter="8"),one))}

    What am I doing wrong?

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

    Re: Need help with SUM(IF (Excel 97)

    How do you want to compare the rdate and pdate ranges? The pdate range contains 30 dates in ascending order, and rdate contains 43 dates in descending order.

  3. #3
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need help with SUM(IF (Excel 97)

    Sorry for the lack of clarity.

    If any of the dates in the rdate column are a match for G3 (in the pdate column, Sheet1). Does this help?

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

    Re: Need help with SUM(IF (Excel 97)

    Sorry, I don't understand. G3 cannot match any date. Please try to state EXACTLY what you want, perhaps by providing a sample calculation of what the result should be.

  5. #5
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need help with SUM(IF (Excel 97)

    Typo, meant cell A3. I have re-attached the spreadsheet with part of the formula in Cell G3 of Sheet1. I need to add additional criteria to this formula. The result should be 40,584...which is a match for for the date of 06/01/2004, and inserter 8 (from Sheet2, column [img]/forums/images/smilies/cool.gif[/img].

  6. #6
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need help with SUM(IF (Excel 97)

    Thanks, this is exactly what I needed. Any ideas why it would not work with defined column names?

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

    Re: Need help with SUM(IF (Excel 97)

    It would work if you defined inserter as Sheet2!B2:B44 etc.

  8. #8
    Lounger
    Join Date
    Feb 2004
    Location
    Buffalo, New York, Wales
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need help with SUM(IF (Excel 97)

    In cell G3 place this formula.

    =SUM(IF((Sheet2!$A$2:$A$44=A3)*(Sheet2!$B$2:$B$44= "8"),Sheet2!$C$2:$C$44))

    use Ctrl-Shift-Enter to place it because it is an array formula. Copy it down and that should do it for you.


    yoyoPHIL

Posting Permissions

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