Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    sumproduct (xl2003)

    With apologies to Hans and others, I'm not very good at this
    I have named ranges called yearsold and totaldue
    I want a count of members under 28 for whom totaldue is not zero.
    I tried this
    =SUMPRODUCT((yearsold<28)*(totaldue<>0))
    It give the #NUM! error message.
    What am I doing wrong, please?

  2. #2
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sumproduct (xl2003)

    sorry, that should be the #VALUE! error message

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

    Re: sumproduct (xl2003)

    The ranges yearsold and totaldue should have the same size. The formula should work then.

    If you still have problems, could you attach the workbook (with sensitive information altered or removed)?

  4. #4
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sumproduct (xl2003)

    the totaldue range is
    =data!$AW$2:$AW$1000
    and the yearsold range is
    =data!$Y$2:$Y$1000,
    Hans

  5. #5
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sumproduct (xl2003)

    HA-Ha! Got it!
    One member had a wrongly-entered date of birth, so the formula generating her age generated the #value! error.
    Now fixed.
    Sorry about false alarm.
    Thanks anyway!

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

    Re: sumproduct (xl2003)

    It's always the little things... <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

Posting Permissions

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