Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    problem with excel formula (2003)

    In attached file - Trying to fill in cell D15.I CANT GET IT RIGHT FOR SOME REASON - I NEED TO SEE 58 IN THAT BOX!!
    Here's what ive got so far...
    =SUM((Data!$G$2:$G$5001>=$A15)*(Data!$G$2:$G$5001< =$B15)*(Data!$J$2:$J$5001=$D$2))

    Thanks
    Attached Files Attached Files
    Regards
    Gerbil (AKA Kevin)

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

    Re: problem with excel formula (2003)

    You should either confirm the formula with Ctrl+Shift+Enter to make it an array formula, or use

    =SUMPRODUCT((Data!$G$2:$G$5001>=$A15)*(Data!$G$2:$ G$5001<=$B15)*(Data!$J$2:$J$5001=$D$2))

    which is an implicit array formula. But there is a problem: in calculations, empty cells equal 0, so the formula also counts all empty rows. So you must add a test to exclude empty rows:

    =SUMPRODUCT((Data!$G$2:$G$5001>=$A15)*(Data!$G$2:$ G$5001<=$B15)*(Data!$J$2:$J$5001=$D$2)*NOT(ISBLANK (Data!$A$2:$A$5001)))

    This returns 58.

  3. #3
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: problem with excel formula (2003)

    Thank you very much!! You are the man!
    Regards
    Gerbil (AKA Kevin)

Posting Permissions

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