# Thread: 'Like' Operator Formula (Excel 2000)

1. ## '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.

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

=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
•