PDA

View Full Version : Solved: Sumproduct Syntax



Hoopsah
06-22-2009, 03:25 AM
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
06-22-2009, 04:55 AM
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
06-22-2009, 06:28 AM
Hi Bob,

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

Cheers

Gerry

Bob Phillips
06-22-2009, 07:08 AM
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
06-22-2009, 07:26 AM
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
06-22-2009, 07:51 AM
I think it is

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

Hoopsah
06-23-2009, 12:06 AM
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
06-23-2009, 12:12 AM
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
06-23-2009, 12:47 AM
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
06-23-2009, 12:56 AM
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