counting unique values conditionally

J

jake

hello,
let's say I wanted to count the number of names that appear in column A only
during the month of January in Column B. However I do not want to count each
name more than once. Like below, I would only like to count Jim's name once
so the answer would be 2 (Jim and Steve).

col. A col. B
Jim 1/05/09
Dan 2/11/09
Steve 1/28/09
Jim 1/16/09

Please Help!
 
T

T. Valko

That will return an incorrect result if a name is associated with dates from
different months. For example, using the OP's sample data change one of Jim's
dates to a different month.

Try this array formula** :

Assuming no empty cells in either column of data.

=COUNT(1/FREQUENCY(IF(MONTH(B2:B5)=1,MATCH(A2:A5,A2:A5,0)),ROW(A2:A5)-ROW(A2)+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.
 
T

T. Valko

Man, I hate this web interface!

The thing that I find so negative about using the web interface is that you
can't easily find or watch posts that you've replied to to catch any
follow-ups that may come.

If you search on your user name the results are not sorted in any order what
so ever.
 
M

Max

If you search on your user name the results are not sorted
in any order what so ever.

Agreed, the search feature is imperfect,
and as you observed, results are haphazardly unsorted
.. you can't easily find or watch posts that you've replied to
to catch any follow-ups that may come.

There's actually a notification feature "Notify me of replies" that you can
activate when you respond in the web interface. Or, activate even when you
have just a 3rd party interest in the thread/discussion (ie being neither
the OP nor responder). This feature works fairly ok from my experience when
the interface is stable, and one can simply click on the link in the email
notification received to zap directly to that particular thread.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,500 Files:370 Subscribers:66
xdemechanik
---
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top