Solved: Sumproduct Syntax

[Hoopsah]

Hi

wonder if someone can look at this and point out where I am going wrong.

I think that this should count 1 after 24/04/09 but I keep getting an answer of 0

I have attached a copy of my worksheet

all help appreciated

Cheers

Hoopsah

[Bob Phillips]

What exactly is the logic here Gerry, in business speak? If you are only checking a single date there is no need for SP.

[Hoopsah]

Hi Bob,

the list shows deleted full time employees, so the list will grow throughout the year,

Cheers

Gerry

[Bob Phillips]

I sorta gathered that Gerry, so I am no wiser yet.

ARe you trying to count how many people work there for a date, or how many left on that date (or in that week)?

[Hoopsah]

Sorry Bob,

I was hoping to show how many people had left after a certain date, so I was trying to count how many people had a start date (Deleted_FTE Column E) that is less than the date in column B and that also had a leaving date (Deleted_FTE Column F) that was greater than column B.

So, with the spreadsheet supplied it should count zero until it reaches row 7, 27/04/09, and then count 1.

I am obviously doing something really wrong though???

[Bob Phillips]

I think it is

=SUMPRODUCT(--(Deleted_FTE!$E$1:$E$2<=B3),--(Deleted_FTE!$F$1:$F$2<=B3))

[Hoopsah]

Fantastic!!

As usual Bob, your formula works perfectly.

However, do you know how would I specify the range so that I don't need to keep amending it as the list grows?

[mdmackillop]

Instead of "Deleted_FTE!$E$1:$E$2" create a Dynamic Range Name In this case
say DelFTE1 using the Formula =Offset($E$1,0,0,Counta($E:$E),1)

[Hoopsah]

OK, hope I have grasped this correctly - it seems to be working on my spreadsheet so I guess I have.

I named the first rangeDelFTE1 using mdmackillop's formula of =OFFSET($E$1,0,0,COUNTA($E:$E),1) and created a second range of =OFFSET($F$1,0,0,COUNTA($F:$F),1)

The used the formula =SUMPRODUCT(--(DelFTE1<=Q3),--(DelFTE2<=Q3))

Success.

Thanks for your help MD & Bob

Cheer - marking as solved

Gerry

[mdmackillop]

Hi Gerry
Just for info (and future use) you can simplify subsequent ranges by offsetting from the first range.
so DelFTE2 has the formula =Offset(DelFTE1,0,1)
Regards
Malcolm