conditional sums

[Brad White]

rows 7 through 200 of tasks

Column A has project names
Column B has names of individuals assigned to tasks
Column C has estimates

In a chart I have
Column A has the project names, no repeats
1) I want to sum column C for a project if someone is assigned.
This works for summing a project, but includes tasks with no one assigned
=SUMIF('Sprint Backlog'!$A$7:$A$200,$A10,'Sprint Backlog'!C$7:C$200)

2) I want to count the Project tasks with someone assigned.

This works to count for a project, but includes tasks with no one assigned
=COUNT(IF(('Sprint Backlog'!$A$7:$A$200=A8)*('Sprint Backlog'!C$7:C$200>0),
'Sprint Backlog'!C$7:C$200))



These don't work:

=COUNT(IF(('Sprint Backlog'!$A$7:$A$200=A8)*('Sprint
Backlog'!C$7:C$200>0)*('Sprint Backlog'!$B$7:$B$200<>""), 'Sprint
Backlog'!C$7:C$200))

=SUM(IF(('Sprint Backlog'!$A$7:$A$200=$A8)*('Sprint
Backlog'!C$7:C$200>0)*('Sprint Backlog'!$B$7:$B$200<>""), 1, 0))



Thanks for any help.

Brad.

 

[Bob Phillips]

=SUMPRODUCT(--(A1:A1000="Project1"),--(B1:B1000<>""),C1:C1000)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Brad White]

=SUMPRODUCT(--(A1:A1000="Project1"),--(B1:B1000<>""),C1:C1000)

Thanks Bob. That helped a lot.

I noticed that you put -- in this answer and a number of other
answers. I've never seen -- in an equation before.
I originally assumed that you were just highlighting or something.
But the equation doesn't work without it.
And it's particular. You *can't* put it in before the last item in
the list. But I can't find -- mentioned in the help anywhere.
What's the trick?

Thanks,
Brad.

 

[Bob Phillips]

Brad, it's all explained here

http://www.xldynamic.com/source/xld.SUMPRODUCT.html
Multiple Condition Tests (using SUMPRODUCT)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Brad White]

Brad, it's all explained here

http://www.xldynamic.com/source/xld.SUMPRODUCT.html
Multiple Condition Tests (using SUMPRODUCT)

Yup.

Thanks again Bob.