# SUMPRODUCT & LEFT functions causing unexpected result

#### kos666

##### New Member
Hello,

I am using the following in a sheet:-

=SUMPRODUCT((LEFT(Sheet1!\$A\$2:\$A\$9963,2)="50")*(Sheet1!\$Q\$2:\$Q\$9963>=\$A30)*(Sheet1!\$N\$2:\$N\$9963=\$C4))

Which returns the value of 5, which is what I want it to do. To help it be more dynamic I want to change the first sumproduct condition ="50" to point to a cell location, so I can change this at leisure without having to recode.

I changed it show =\$A31, however the returned the value is now 0

So obviously something is wrong, but I can't figure out what.

I have also tried:-

=A31
=\$A\$31
=VALUE(\$A31)
=VALUE(\$A\$31)
=VALUE(A31)

There's obviously some lack of knowledge on my part. So cap in hand... Any ideas please?

Kind Regards,

### Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Try

=SUMPRODUCT((LEFT(Sheet1!\$A\$2:\$A\$9963,2)=\$A31&"")*(Sheet1!\$Q\$2:\$Q\$9963>=\$A30)*(Sheet1!\$N\$2:\$N\$9963=\$C4))

Try this:

=TEXT(A31,0)

Sounds like a numeric vs Text comparison problem.

They both worked as I needed.

Thank you for your prompt assistance.

Kind Regards,

Replies
3
Views
301
Replies
4
Views
451
Replies
1
Views
189
Replies
1
Views
273
Replies
2
Views
225

1,219,828
Messages
6,150,473
Members
450,966
Latest member
Yali

### 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