I am using this sumproduct function in our project summary log to retrieve a
range of cells that contains "Richard Horn" (project Lead) but not if they
contain "carried forward" or "completed" (project statuses).

=SUMPRODUCT(('Q4'!D9:D39="Richard
Horn")*('Q4'!E9:E39<"completed")*('Q4'!E9:E39<"c arried
forward")*('Q4'!E9:E39<"on hold"))

What I also wanted to do was to say only pull those within a specific date
range like below.

=COUNTIF(I9:I39,"=10/01/09")-COUNTIF(I9:I39,"12/31/09")

Can I combine or re-write this function into one function?

Thanks, Richard

Hi Richard,

Just add the conditions to the sumproduct formula:

=SUMPRODUCT(--('Q4'!D9:D39="Richard
Horn"),--('Q4'!E9:E39<"completed"),--('Q4'!E9:E39<"carried
forward"),--('Q4'!E9:E39<"on
hold"),--('Q4'!I9:I39=DATEVALUE("10-01-2009")),--('Q4'!I9:I39<=DATEVALUE("12-31-2009")))

Regards,
Per

"Richard Horn" skrev i meddelelsen
...
I am using this sumproduct function in our project summary log to retrieve
a
range of cells that contains "Richard Horn" (project Lead) but not if they
contain "carried forward" or "completed" (project statuses).

=SUMPRODUCT(('Q4'!D9:D39="Richard
Horn")*('Q4'!E9:E39<"completed")*('Q4'!E9:E39<"c arried
forward")*('Q4'!E9:E39<"on hold"))

What I also wanted to do was to say only pull those within a specific date
range like below.

=COUNTIF(I9:I39,"=10/01/09")-COUNTIF(I9:I39,"12/31/09")

Can I combine or re-write this function into one function?

Thanks, Richard

Richard,

Try this

=SUMPRODUCT(('Q4'!D9:D39="Richard
Horn")*('Q4'!E9:E39<"completed")*('Q4'!E9:E39<"c arried
forward")*('Q4'!E9:E39<"on
hold")*(I9:I39=DATE(2009,10,1))*(I9:I39<=DATE(200 9,12,1)))

Mike

"Richard Horn" wrote:

I am using this sumproduct function in our project summary log to retrieve a
range of cells that contains "Richard Horn" (project Lead) but not if they
contain "carried forward" or "completed" (project statuses).

=SUMPRODUCT(('Q4'!D9:D39="Richard
Horn")*('Q4'!E9:E39<"completed")*('Q4'!E9:E39<"c arried
forward")*('Q4'!E9:E39<"on hold"))

What I also wanted to do was to say only pull those within a specific date
range like below.

=COUNTIF(I9:I39,"=10/01/09")-COUNTIF(I9:I39,"12/31/09")

Can I combine or re-write this function into one function?

Thanks, Richard

Mike,

I think this is very close but I have an issue: The first formula seems ok
becasue there are 2 projects between 1/1/2010 and 1/31/2010.

=SUMPRODUCT(('Q4'!D9:D39="Chris
Craig")*('Q4'!E9:E39<"completed")*('Q4'!E9:E39<" carried
forward")*('Q4'!E9:E39<"on
hold")*('Q4'!F9:F39=DATE(2010,1,1))*(F9:F39<=DATE (2010,1,31)))

The second formula however is returning 5 which is incorrect. Chris has 6
projects total: one is on hold (don't count), 2 projects that are due in
Januray 2010 (don't count). The formula is returning 5 but it should return
3. Chris has 3 projects that are due on 10/31/09 that are not on hold or
carried forward.

=SUMPRODUCT(('Q4'!D9:D39="Chris
Craig")*('Q4'!E9:E39<"completed")*('Q4'!E9:E39<" carried
forward")*('Q4'!E9:E39<"on
hold")*('Q4'!F9:F39=DATE(2009,9,31))*(F9:F39<=DAT E(2009,12,31)))

"Mike H" wrote:

Richard,

Try this

=SUMPRODUCT(('Q4'!D9:D39="Richard
Horn")*('Q4'!E9:E39<"completed")*('Q4'!E9:E39<"c arried
forward")*('Q4'!E9:E39<"on
hold")*(I9:I39=DATE(2009,10,1))*(I9:I39<=DATE(200 9,12,1)))

Mike

"Richard Horn" wrote:

I am using this sumproduct function in our project summary log to retrieve a
range of cells that contains "Richard Horn" (project Lead) but not if they
contain "carried forward" or "completed" (project statuses).

=SUMPRODUCT(('Q4'!D9:D39="Richard
Horn")*('Q4'!E9:E39<"completed")*('Q4'!E9:E39<"c arried
forward")*('Q4'!E9:E39<"on hold"))

What I also wanted to do was to say only pull those within a specific date
range like below.

=COUNTIF(I9:I39,"=10/01/09")-COUNTIF(I9:I39,"12/31/09")

Can I combine or re-write this function into one function?

Thanks, Richard

Hello Richard,

You can easily have it for all your project leads without any formulae
if you use pivot tables:
http://www.contextures.com/CreatePivotTable.html

Regards,
Bernd

You have missed few sheet references...

=SUMPRODUCT(('Q4'!D9:D39="Richard Horn")*
('Q4'!E9:E39<"completed")*('Q4'!E9:E39<"carried forward")*
('Q4'!E9:E39<"on hold")*('Q4'!I9:I39=DATE(2009,10,1))*
('Q4'!I9:I39<=DATE(2009,12,1)))

If this post helps click Yes
---------------
Jacob Skaria


"Richard Horn" wrote:

I am using this sumproduct function in our project summary log to retrieve a
range of cells that contains "Richard Horn" (project Lead) but not if they
contain "carried forward" or "completed" (project statuses).

=SUMPRODUCT(('Q4'!D9:D39="Richard
Horn")*('Q4'!E9:E39<"completed")*('Q4'!E9:E39<"c arried
forward")*('Q4'!E9:E39<"on hold"))

What I also wanted to do was to say only pull those within a specific date
range like below.

=COUNTIF(I9:I39,"=10/01/09")-COUNTIF(I9:I39,"12/31/09")

Can I combine or re-write this function into one function?

Thanks, Richard