Combining Functions (Sumproduct and Countif)

[Richard Horn]

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'!D939="Richard
Horn")*('Q4'!E9:E39<>"completed")*('Q4'!E9:E39<>"carried
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

 

[Per Jessen]

Hi Richard,

Just add the conditions to the sumproduct formula:

=SUMPRODUCT(--('Q4'!D939="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

 

[Mike H]

Richard,

Try this

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

Mike

 

[Bernd P]

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

 

[Richard Horn]

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'!D939="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'!D939="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<=DATE(2009,12,31)))

 

[Jacob Skaria]

You have missed few sheet references...

=SUMPRODUCT(('Q4'!D939="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