Question about Count Distinct

[shdawson]

Good Morning,


Need to count the distinct entries in 1 field, based upon the entries in a 2nd field.

Need to get a list of project manager names from the project_manager field. Need to do that based upon what the entry is in the status field for their record (row), if that entry is a Y.

Thinking through SUMPRODUCT. Not seeing the light at the end of the path.

Thinking through http://office.microsoft.com/en-us/e...nique-values-in-a-data-range-HP003056118.aspx Still not seeing that light at the end of the path.

Hmmm......any thoughts?


Thanks,
S

 

[yogeshmaney]

kindly post data sample please

 

[shdawson]

Hi,


Thanks for chiming in on this.

Excel Workbook
	E	F	G
1	project_number	status	project_manager
2	123	Y	jimmy
3	456	Y	billy
4	789	Y	tommy
5	1111	N	jimmy
6	2222	N	jimmy
7	3333	N	billy
8	4444	N	tommy
Sheet1

Excel 2003

Looking for a count of 3, since I have 3 distinctly differently project_manger names with the status of Y.

Thank you, kindly.


S

 

[villy]

Not clear for me..any more clue?

 

[shdawson]

IF status="Y"
Count Distinct project_manager

ELSE...go drink coffee (HA!)


How is that for a generic illustration?


S

 

[yogeshmaney]

Try This


{=COUNT(IF($H$2:$H$8=INDEX($H$2:$H$8,MATCH(0,COUNTIF($L$1:L1,$H$2:$H$8),0)),IF($G$2:$G$8="Y",$F$2:$F$8)))}


Array Formula

 

[T. Valko]

Hi,


Thanks for chiming in on this.

Excel Workbook
	E	F	G
1	project_number	status	project_manager
2	123	Y	jimmy
3	456	Y	billy
4	789	Y	tommy
5	1111	N	jimmy
6	2222	N	jimmy
7	3333	N	billy
8	4444	N	tommy
Sheet1

Excel 2003



Looking for a count of 3, since I have 3 distinctly differently project_manger names with the status of Y.

Thank you, kindly.


S

Try this array formula**:

=SUM(IF(FREQUENCY(IF(F2:F8="Y",MATCH(G2:G8,G2:G8,0)),ROW(G2:G8)-ROW(G2)+1),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Assumes no empty cells within the data range of column G.

 

[yogeshmaney]

kindly replace col L with where u gonna put that formula. I tested on L1

 

[shdawson]

Hi,


Thanks for the effort, guys.

yogeshmaney, the formula returns 0. I then put the formula in L1, and it says there is an error.
T. Valko, the formula returns 3.


Not sure what is off with the formula, yogeshmaney. I will go with the formula from T. Valko with great thanks.


Kindest Regards,
S

 

[shdawson]

BTW..."Assumes no empty cells within the data range of column G."


That could happen, but not sure why. What will the formula return if there is an empty cell? I messed with it and found it returns #N/A.

Is there an easy way to edit the formula to account for empty cells? There is always a project manager coming and going, as well as a project going between active and non active status. To that end, it is an ever changing data range.


Thanks,
S