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

  • Thread starter Thread starter jeffc
  • Start date Start date
J

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
 
=SUMPRODUCT(--($A$2:$A$200=A1),--($C$2:$C$200<>""))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
this modified formula should work a little better.

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



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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
Click to expand...
 
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 <>""?
 
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<>"")),"")
 
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)
 
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.
 
Sloth,

This did it. Excellent!

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

Thanks for the help.

Jeff
 
Back
Top