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

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


--
jeffc
------------------------------------------------------------------------
jeffc's Profile: http://www.excelforum.com/member.php...o&userid=33452
View this thread: http://www.excelforum.com/showthread...hreadid=532621

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

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"jeffc" wrote in
message ...

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


--
jeffc
------------------------------------------------------------------------
jeffc's Profile:
http://www.excelforum.com/member.php...o&userid=33452
View this thread: http://www.excelforum.com/showthread...hreadid=532621

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

this modified formula should work a little better.

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



"Bob Phillips" wrote:

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"jeffc" wrote in
message ...

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


--
jeffc
------------------------------------------------------------------------
jeffc's Profile:
http://www.excelforum.com/member.php...o&userid=33452
View this thread: http://www.excelforum.com/showthread...hreadid=532621

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

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 <""?

In article ,
Sloth wrote:

this modified formula should work a little better.

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

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

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<"")),"")



"JE McGimpsey" wrote:

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 <""?

In article ,
Sloth wrote:

this modified formula should work a little better.

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

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

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)

"Sloth" wrote in message
...
this modified formula should work a little better.

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



"Bob Phillips" wrote:

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"jeffc" wrote in
message ...

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


--
jeffc

------------------------------------------------------------------------
jeffc's Profile:
http://www.excelforum.com/member.php...o&userid=33452
View this thread:
http://www.excelforum.com/showthread...hreadid=532621

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

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.



"Bob Phillips" wrote:

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)

"Sloth" wrote in message
...
this modified formula should work a little better.

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



"Bob Phillips" wrote:

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"jeffc" wrote in
message ...

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


--
jeffc

------------------------------------------------------------------------
jeffc's Profile:
http://www.excelforum.com/member.php...o&userid=33452
View this thread:
http://www.excelforum.com/showthread...hreadid=532621

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

Sloth,

This did it. Excellent!

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

Thanks for the help.

Jeff


Sloth Wrote:
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<"")),"")



"JE McGimpsey" wrote:

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 <""?

In article ,
Sloth wrote:

this modified formula should work a little better.

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




--
jeffc
------------------------------------------------------------------------
jeffc's Profile: http://www.excelforum.com/member.php...o&userid=33452
View this thread: http://www.excelforum.com/showthread...hreadid=532621

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

Thanks to everyone who helped out on this.

Now I can get some sleep tonight! ;)

Jeff


--
jeffc
------------------------------------------------------------------------
jeffc's Profile: http://www.excelforum.com/member.php...o&userid=33452
View this thread: http://www.excelforum.com/showthread...hreadid=532621

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

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"jeffc" wrote in message
...

Sloth,

This did it. Excellent!

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

Thanks for the help.

Jeff


Sloth Wrote:
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<"")),"")



"JE McGimpsey" wrote:

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 <""?

In article ,
Sloth wrote:

this modified formula should work a little better.

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




--
jeffc
------------------------------------------------------------------------
jeffc's Profile:
http://www.excelforum.com/member.php...o&userid=33452
View this thread: http://www.excelforum.com/showthread...hreadid=532621