Count Distinct

[Johnny]

I've scanned through related posts with no luck. I have a production
spreadsheet with several columns of data. Here are the relevant columns (3)
for my question:

City Employee Date


I need to count the distinct number of employees based on the city and a
date range. Let's say that the date range is 1/1/09 to 1/31/09 and is in
cells A1 and A2 respectively.

Cities and Employees are duplicated multiple times. What I want to end up
with is the count of the number of emloyees for a particular City for a
given date range.

 

[Jacob Skaria]

--With no blank records try the below array formula which will return the
distinct count for Jan2009.

--Edit the "city"

=SUM(N(FREQUENCY(IF((TEXT(C2:C8,"mmyyyy")="012009")*
(A2:A8="city"),MATCH(B2:B8,B2:B8,)),MATCH(B2:B8,B2:B8,))>0))


Please note that this is an array formula. You create array formulas in the
same way that you create other formulas, except you press CTRL+SHIFT+ENTER to
enter the formula. If successful in 'Formula Bar' you can notice the curly
braces at both ends like "{=<formula>}"

If this post helps click Yes

 

[JBeaucaire]

Column A: City names
Column B: Employee names
Column C: Dates

E2: a city name to match
E3: Starting date
E4: Ending date

Formula:
=SUM(INDEX(($A$1:$A$13=E2) * ($C$1:$C$13>=E3) * ($C$1:$C$13<=E4) *
(MATCH($B$1:$B$13,$B$1:$B$13,0)=ROW($A$1:$A$13)),0))

This formula requires the range of cells be exactly listed, no extra range,
so my example goes down 13 rows. Adjust yours accordingly.

Does that help?

 

[T. Valko]

Try this...

A1 = lower date boundary
A3 = upper date boundary
A3 = some city

In the formula:

City refers to the range that contains the city names
Emp refers to the range that contains the employee names
Date refers to the range that contains the dates

Array entered** :

=SUM(IF(FREQUENCY(IF(City=A3,IF(Date>=A1,IF(Date<=A2,MATCH(Emp,Emp,0)))),ROW(Emp)-MIN(ROW(Emp))+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 there are no empty cells in the Employee range.

 

[JBeaucaire]

Or a simpler SUMPRODUCT() syntax:

=SUMPRODUCT((($A$1:$A$13=E2) * ($C$1:$C$13>=E3) * ($C$1:$C$13<=E4)) *
(MATCH($B$1:$B$13,$B$1:$B$13,0) = ROW($A$1:$A$13)))

 

[Johnny]

That worked for dates in the range 1/1/09 - 1/30/09. However, when I change
the date range to Feb. (2/1 - 2/28) or any other month, it's not working for
some unknown reason. My actual range is row 2 to 4093 which contains work
items for the entire year. What I am after is the number of employees for
each City for each month of the year. Your formula works perfect when I have
data for each month on separte worksheets.

Please let me know if you need additional information.

 

[Johnny]

That worked. Thank you so much.

I accidently click on "No" to the question on whether your post was helpful.
Sorry about that. Your post was very very helpful.

 

[T. Valko]

You're welcome. Thanks for the feedback!