counting unique values conditionally
 

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!

counting unique values conditionally
 

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

Success? Click YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,500 Files:370 Subscribers:66
xdemechanik
---
"jake" wrote:
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

counting unique values conditionally
 

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.

--
Biff
Microsoft Excel MVP


"Max" wrote:

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

Success? Click YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,500 Files:370 Subscribers:66
xdemechanik
---
"jake" wrote:
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

counting unique values conditionally
 

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

counting unique values conditionally
 

Man, I hate this web interface!


--
Biff
Microsoft Excel MVP


"Max" wrote:

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

counting unique values conditionally
 

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.


--
Biff
Microsoft Excel MVP


"T. Valko" wrote:

Man, I hate this web interface!


--
Biff
Microsoft Excel MVP


"Max" wrote:

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

counting unique values conditionally
 

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

counting unique values conditionally
 

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

--
Biff
Microsoft Excel MVP


"Max" wrote in message
...
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
---