Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
MS Excel
How to count unique values of a column corresponding to a respective repeated
values of a column. For example there are multiple month entries in a column, and for every month entry there are multiple employee names. How to count the number of employees in a month. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
MS Excel
=COUNTIF(A1:A100,"March")
if the month and employees match 1:1 and the month is a name not a date. Otherwise =SUMPRODUCT(--(MONTH(A1:A100)=3),--(B1:B100<"")) -- HTH RP (remove nothere from the email address if mailing direct) "Expert N" <Expert wrote in message ... How to count unique values of a column corresponding to a respective repeated values of a column. For example there are multiple month entries in a column, and for every month entry there are multiple employee names. How to count the number of employees in a month. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
MS Excel
If employee entries are unique and you want to count employee entries
=countif(A:A,"=1/1/2005")-countif(A:A,"1/31/2005") if you have multiple entries for each employee in a single month and want a unique count for that month, look at these past postings by Frank Kabel and Harlan Grove: Harlan Grove: You mean select records in which column A in the table matches cell A4, then count the distinct entries in column B in the table? If so, try the array formula =SUM(ISNUMBER(MATCH('Sheet 4'!C1:C100,IF('Sheet 4'!B1:B100=A4, 'Sheet 4'!C1:C100),0))/COUNTIF('Sheet 4'!C1:C100,'Sheet 4'!C1:C100&"")) Frank Kabel: Hi one way: Try the following array formula (entered with CTRL+SHIFT+ENTER): =SUM(1/MMULT(--(IF('Sheet 4'!B1:B100=A4,'Sheet 4' !C1:C100)=TRANSPOSE(IF('Sheet 4'!B1:B100=A4,'Sheet 4' !C1:C100))),ROW(C1:C100)^0))-1 You would need to adapt these to work with the month number -- Regards, Tom Ogilvy "Expert N" <Expert wrote in message ... How to count unique values of a column corresponding to a respective repeated values of a column. For example there are multiple month entries in a column, and for every month entry there are multiple employee names. How to count the number of employees in a month. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|