| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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<"")),"") |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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<"")),"") |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I count distinct names? | Excel Worksheet Functions | |||
| count of (blank) pivot table | Excel Discussion (Misc queries) | |||
| How do I count different names in a colum ie: 4 mikes 3 toms | Excel Discussion (Misc queries) | |||
| Count Non Blank in column | Excel Discussion (Misc queries) | |||
| count non blank cells which meet criteria in another column | Excel Worksheet Functions | |||
Linear Mode
