Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    New Jersey
    Posts
    103
    Thanks
    6
    Thanked 0 Times in 0 Posts

    'Like' Operator Formula (Excel 2000)

    I have a formula as follows:
    =SUMPRODUCT(((NH!$I$1:$I$2000=25)+(NH!$I$1:$I$2000 =26))*(NH!$K$1:$K$2000="A")*(NH!$N$1:$N$2000="ACT" )) What I would like to do is change the last part to include any codes beginning with "ACT". I can't seem to find a operator that will do that.

    Please help! <img src=/S/bwaaah.gif border=0 alt=bwaaah width=123 height=15>

  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: 'Like' Operator Formula (Excel 2000)

    Have you tried:
    =SUMPRODUCT(((NH!$I$1:$I$2000=25)+(NH!$I$1:$I$2000 =26))*(NH!$K$1:$K$2000="A")*(left(NH!$N$1:$N$2000, 3)="ACT"))

    Steve

  3. #3
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    New Jersey
    Posts
    103
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: 'Like' Operator Formula (Excel 2000)

    Excellent! that worked perfectly. <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>
    One more question....

    How about if I want to select people who are >25 but less than 31. I tried this but the last part didn't work.

    =SUMPRODUCT(((NH!$I$1:$I$2000=2)+(NH!$I$1:$I$2000= 5))*(NH!$N$1:$N$2000="ACT")*(NH!$H$1:$H$2000 >=26<31 ))

  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: 'Like' Operator Formula (Excel 2000)

    How about this:
    =SUMPRODUCT(((NH!$I$1:$I$2000=2)+(NH!$I$1:$I$2000= 5))*(NH!$N$1:$N$2000="ACT")*(NH!$H$1:$H$2000 >=26)*(NH!$H$1:$H$2000 <31 ))

    Steve

Posting Permissions

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