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!

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

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

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

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

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