Count Names in One Col if 2nd Col Isn't Blank

[jeffc]

This one's got me stumped so any help would be appreciated.

Here's the problem, I've got one column with names in it and a second
column with dates in it. What I'm trying to do is generate a count in a
third column next to each name with the number of times the name appears
in the name column when there is a date in the corresponding date
column. If the date column is blank I want the count column to remain
blank.

For example:

Assigned To: Number Completed Date
Joe 2 1/1/06
Jane
Joe 2 2/22/06


I know that there will be repeated info but the end user doesn't care.


Thanks
Jeff

 

[Bob Phillips]

=SUMPRODUCT(--($A$2:$A$200=A1),--($C$2:$C$200<>""))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

this modified formula should work a little better.

=IF(C1<>"",SUMPRODUCT(--($A$2:$A$200=A2),--($C$1:$C$200<>"")),"")

=SUMPRODUCT(--($A$2:$A$200=A1),--($C$2:$C$200<>""))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[JE McGimpsey]

I'd think it would give an error, since the ranges aren't the same size.

Why check the "Date" label? Won't it always be <>""?

 

[Guest]

oops, that's what I get for rushing, sorry about that. The point I was
trying to make was
1) change the A1 to A2, since the first formula will first be pasted in B2.
2) Add the conditional statement because according to how I understood the
question, only the completed ones will have a date next to it. In the
example next to Jane is nothing, not 0. And if Jane had other rows that had
dates, there would incorrectly be a number next to it in the formula as
originaly posted.

=IF(C2<>"",SUMPRODUCT(--($A$2:$A$200=A2),--($C$2:$C$200<>"")),"")

 

[Bob Phillips]

Errors apart, it seems a bit redundant to explicitly test for C being blank
and then do so again in the SP part. If you do that test, COUNTIF would be
somewhat better.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

Example

Your formula results:
Assigned To: Number Completed Date
Joe 2 1/1/06
Jane 1
Joe 2 2/22/06
Jane 1 3/12/06
Jim 0

My formula (with errors fixed) results:
Assigned To: Number Completed Date
Joe 2 1/1/06
Jane
Joe 2 2/22/06
Jane 1 3/12/06
Jim

This is what I thought the OP wanted, so I thought it might be slightly more
appropriate.

 

[jeffc]

Sloth,

This did it. Excellent!

Now, if you feel like answering one more question. What function do
the -- serve.

Thanks for the help.

Jeff

 

[jeffc]

Thanks to everyone who helped out on this.

Now I can get some sleep tonight!

Jef

 

[Bob Phillips]

See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed
explanation.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)