# Excel: 'Is like' within formula

#### GaryStone10

##### Board Regular
Hi All

I have this formula below

=SUMPRODUCT(--(RAW_DATA!\$B\$4:\$B\$100=Dashboard!\$I\$10),--(RAW_DATA!\$A\$4:\$A\$100='Dashboard Data'!\$A2),--(RAW_DATA!\$C\$4:\$C\$100=Dashboard!\$I\$10),--(RAW_DATA!\$J\$4:\$J\$100))

Which works fine, but....

I want it to pick up things that are not =Dashboard!\$I\$10 but start with that value.

Eg if Dashboard!\$I\$10 = Bob I want it to evaluate for all terms such as Bob1, Bob2, Bob3, etc.

Or to put it another way, I want to be able to use a Wildcard (*) with part of a formula.

### Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Is it?

=SUMPRODUCT(--(LEFT(RAW_DATA!\$B\$4:\$B\$100,LEN(Dashboard!\$I\$10))=Dashboard!\$I\$10),--(RAW_DATA!\$A\$4:\$A\$100='Dashboard Data'!\$A2),--(RAW_DATA!\$C\$4:\$C\$100=Dashboard!\$I\$10),--(RAW_DATA!\$J\$4:\$J\$100))

try

=SUMPRODUCT(--(LEFT(raw_data!\$B\$4:\$B\$100,LEN(Dashboard!\$I\$10))=Dashboard!\$I\$10),--(raw_data!\$A\$4:\$A\$100='Dashboard data'!\$A2),--(raw_data!\$C\$4:\$C\$100=Dashboard!\$I\$10),--(raw_data!\$J\$4:\$J\$100))

Is it?

=SUMPRODUCT(--(LEFT(RAW_DATA!\$B\$4:\$B\$100,LEN(Dashboard!\$I\$10))=Dashboard!\$I\$10),--(RAW_DATA!\$A\$4:\$A\$100='Dashboard Data'!\$A2),--(RAW_DATA!\$C\$4:\$C\$100=Dashboard!\$I\$10),--(RAW_DATA!\$J\$4:\$J\$100))

It is indeed!

Many thanks.

Replies
2
Views
420
Replies
1
Views
175
Replies
3
Views
232
Replies
1
Views
188
Replies
1
Views
335

1,207,261
Messages
6,077,359
Members
446,279
Latest member
hoangquan2310

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back