Combining Functions (Sumproduct and Countif)
 

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

Combining Functions (Sumproduct and Countif)
 

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

Combining Functions (Sumproduct and Countif)
 

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

Combining Functions (Sumproduct and Countif)
 

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

Combining Functions (Sumproduct and Countif)
 

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

Combining Functions (Sumproduct and Countif)
 

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