Link to home
Create AccountLog in
Avatar of RTsal
RTsal

asked on

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

ASKER CERTIFIED SOLUTION
Avatar of Peter_Werner
Peter_Werner

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of RTsal
RTsal

ASKER

Datevalue works fine - thanks