counting unique values conditionally

[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!

 

[Max]

One try
=SUMPRODUCT((A1:A10<>"")*(TEXT(B1:B10,"mmmyy")="Jan09")/COUNTIF(A1:A10,A1:A10&""))

Success? Click YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,500 Files:370 Subscribers:66
xdemechanik

 

[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.

 

[Max]

.. That will return an incorrect result ..
Agreed. Inadequately tested.
Pl dismiss my earlier response.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,500 Files:370 Subscribers:66
xdemechanik
---

 

[T. Valko]

Man, I hate this web interface!

 

[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.

 

[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
---

 

[T. Valko]

"Notify me of replies"

Yeah, I figured that out after the fact!

when the interface is stable

Lately, that's not very often!

Thanks, Max!