MS Excel

[Guest]

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.

 

[Bob Phillips]

=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)

 

[Tom Ogilvy]

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