Excel sumproduct calculation with LEFT and variable length of characters

I want to add sales figures for products using sumproduct and LEFT. But the search terms I use have variable length of characters. It is difficult to explain but please view the code. In A9 I will enter the first several letters of the product I want to total such as "Sony *". The way the formula works I would have to use the same number of characters for each product I am totaling, in this case 3 letters
(LEFT('2009'!$D$2:$D$65500,3)=A9)
I need it to allow for a varying number of characters in A9

SUMPRODUCT(('2009'!$G$2:$G$65500>=DATEVALUE($A42&" "&$A41&" "&$A40))*('2009'!$G$2:$G$65500<=DATEVALUE($B42&""&$B41&""&$B40))*(LEFT('2009'!$D$2:$D$65500,3)=A9)*('2009'!$H$2:$H$65500))

Open in new window

RTsalAsked:
Who is Participating?
 
Peter_WernerConnect With a Mentor Commented:
LEFT(...,LEN(A9))=A9

You use " " and "" at DATEVALUE().  Don't you need "-" or "/"?
0
 
RTsalAuthor Commented:
Datevalue works fine - thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.