Sumproduct and Text string

[daily106]

Hi all ,

I am trying to sum project IF the first 12 characters of a named range "Tasksname" and the first 12 characters of a1 is a match. What am I doing wrong here? Thank you.

=SUMPRODUCT(--(LEFT(taskname,12)=LEFT(a1,12)),project)

 

[p45cal]

1. What is:
project
? Hopefully a range (a) the same size as Tasksname, and (b) contains numbers.

2. I note you have different spellings of Tasksname and taskname.

3. What is the error (or wrong result) you're getting, and what should it be.

You're a little short on details.

 

[daily106]

1. What is project?
It is the named range of cost for projects. It is the same length.

2. I note you have different spellings of Tasksname and taskname.
It they are the same. My spelling error. Tasksname.

3. What is the error (or wrong result) you're getting, and what should it be.
I am getting VALUE error for this.

I am trying to do is if the first 12 digits of named range of "taskname" is the same as first 12 digits of the cell A1 then add project amounts. Hope I cleared things out. Thank you.

 

[AdityaTyagi]

write it in this way
=SUMPRODUCT((LEFT(taskname,12)=LEFT(a1,12))*project)

 

[p45cal]

Tasksname and project NOT being the same dimension could cause the #VALUE error.
Cut and paste the range definitions (RefersTo for both named ranges here.

 

[daily106]

Thank you all. I got it to work by using the formular below.

{=SUM(IF(LEFT(taskname,16)=LEFT(a1,16),project,0))}

How could I add another condition of named date ranges "dateinformation"?

{=SUM(IF((dateinformation>DATE(2011,1,1))*(LEFT(taskname,16)=LEFT(a1,16),project,0)}