Sum If for named date range

[daily106]

Hi all

I need some help on the following function. dateinformation is named range that include dates. "taskname" and "cost" are also another named range which is the same length as the dateinformation.


What I am trying to do is anything after January of 2011 if the first 16 characters of "taskname" and 16 characters of a1 text matches sum "cost". I am getting #n/a for some reason. Any help would be much appreciated. Thank you.

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

 

[Marcelo Branco]

Hi,

I think a parenthesis is missing in your formula
{=SUM(IF((dateinformation>DATE(2011,1,1))*(LEFT(taskname,16)=LEFT(a1,16)),cost,0)}

As an alternative i think you can use
=SUMPRODUCT(--(dateinformation>DATE(2011,1,1)),--(LEFT(taskname,16)=LEFT(a1,16)),cost)


HTH

M.

 

[daily106]

Thank you very much.

I tried the formular below and now I am getting Value error. I checked the range to make sure they are all correct, but can not see why this is not working.

SUMPRODUCT(--(thrudateinformation>DATE(2010,12,31)),--(LEFT(taskname,13)=LEFT( HD150,13)),project)

 

[T. Valko]

Thank you very much.

I tried the formular below and now I am getting Value error. I checked the range to make sure they are all correct, but can not see why this is not working.

SUMPRODUCT(--(thrudateinformation>DATE(2010,12,31)),--(LEFT(taskname,13)=LEFT( HD150,13)),project)

Tell us what the range addresses are for:

• thrudateinformation
• taskname
• project

What's in HD150?

What's in taskname?

 

[Marcelo Branco]

Thank you very much.

I tried the formular below and now I am getting Value error. I checked the range to make sure they are all correct, but can not see why this is not working.

SUMPRODUCT(--(thrudateinformation>DATE(2010,12,31)),--(LEFT(taskname,13)=LEFT( HD150,13)),project)

And also tell us what you have in the project range: numbers?
All ranges the same size?

M.

 

[daily106]

thrudateinformation: $E$1 title
=OFFSET(TimeSheet!$E$1,0,0,COUNTA(TimeSheet!$E:$E),1)

TaskName: $G$1 title
=OFFSET(TimeSheet!$G$1,0,0,COUNTA(TimeSheet!$G:$G),1)

Project: $L$1 title
=OFFSET(TimeSheet!$L$1,0,0,COUNTA(TimeSheet!$L:$L),1)

Thrudateinformation: Date value ex) 8/27/2010
TaskName: text string ex) 1.80.30.15.03.41 ACP020
Project: general number value ex) 121,658
Cell "HD150" has the same text string as "taskname" ex)1.80.30.15.03.45 ACP020

Is it because of the name ranges include the titles?

 

[Jonmo1]

Do all 3 columns E G and L end at same row?

What do these return
=ROWS(thrudateinformation)
=ROWS(TaskName)
=ROWS(Project)

Are they all the same?

 

[T. Valko]

thrudateinformation: $E$1 title
=OFFSET(TimeSheet!$E$1,0,0,COUNTA(TimeSheet!$E:$E),1)

TaskName: $G$1 title
=OFFSET(TimeSheet!$G$1,0,0,COUNTA(TimeSheet!$G:$G),1)

Project: $L$1 title
=OFFSET(TimeSheet!$L$1,0,0,COUNTA(TimeSheet!$L:$L),1)

Thrudateinformation: Date value ex) 8/27/2010
TaskName: text string ex) 1.80.30.15.03.41 ACP020
Project: general number value ex) 121,658
Cell "HD150" has the same text string as "taskname" ex)1.80.30.15.03.45 ACP020

Is it because of the name ranges include the titles?

Are you absolutely certain that ALL the ranges are the same size?

Are there any empty cells in any of the ranges? If there are then that will lead to incorrect range size evaluation.

 

[daily106]

They all end with the same number of 20043 lines.

 

[Jonmo1]

Are there any #Value! Errors within any of the 3 ranges?