Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Combining Functions (Sumproduct and Countif)

703 views
Skip to first unread message

Richard Horn

unread,
Oct 16, 2009, 9:39:01 AM10/16/09
to
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<>"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

unread,
Oct 16, 2009, 10:05:17 AM10/16/09
to
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" <Richa...@discussions.microsoft.com> skrev i meddelelsen
news:FCDCE4A7-C125-4CCE...@microsoft.com...

Bernd P

unread,
Oct 16, 2009, 10:19:01 AM10/16/09
to
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

Mike H

unread,
Oct 16, 2009, 10:18:03 AM10/16/09
to
Richard,

Try this

=SUMPRODUCT(('Q4'!D9:D39="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

Richard Horn

unread,
Oct 16, 2009, 11:05:01 AM10/16/09
to
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<=DATE(2009,12,31)))

Jacob Skaria

unread,
Oct 16, 2009, 11:09:01 AM10/16/09
to
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

0 new messages