sumproduct of unique instances only

[doug firr]

Picture 2 columns: Col A uniquepersonID | Col B dd/mm/yy |

There can be multiple instances of the same unique person ID - a person can appear in the data numerous times.

For each month of the year I would like to know the number of individuals who appear, not the number of total instances.

The formula =sumproduct((A2:A100>=1/1/12)*(sumproductA2:A100<2/1/12)) would give me the total instances but not unique instances.

How would I tell excel to return only to return unique people in each month, rather than the number of instances?

 

[Fazza]

One way is a variation on this thread from the other day, http://www.mrexcel.com/forum/excel-questions/660748-counting-unique-values.html#post3277702

That thread has two columns plus a date. Your question with one ID column and a date is a simpler version.

regards

 

[Aladin Akyurek]

Picture 2 columns: Col A uniquepersonID | Col B dd/mm/yy |

There can be multiple instances of the same unique person ID - a person can appear in the data numerous times.

For each month of the year I would like to know the number of individuals who appear, not the number of total instances.

The formula =sumproduct((A2:A100>=1/1/12)*(sumproductA2:A100<2/1/12)) would give me the total instances but not unique instances.

How would I tell excel to return only to return unique people in each month, rather than the number of instances?

A formula approach would be:

C2: 1-Jan-12

D2, control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF($A$2:$A$100<>"",
   IF(ISNUMBER($B$2:$B$100),IF($B$2:$B$100-DAY(B$2:$B$100)+1=D2,
      MATCH("~"&$A$2:$A$100,$A$2:$A$100&"",0)))),
        ROW($A$2:$A$100)-ROW($A$2)+1),1))

 

[T. Valko]

Picture 2 columns: Col A uniquepersonID | Col B dd/mm/yy |

There can be multiple instances of the same unique person ID - a person can appear in the data numerous times.

For each month of the year I would like to know the number of individuals who appear, not the number of total instances.

The formula =sumproduct((A2:A100>=1/1/12)*(sumproductA2:A100<2/1/12)) would give me the total instances but not unique instances.

How would I tell excel to return only to return unique people in each month, rather than the number of instances?

Try this...

Book1
	A	B	C	D	E
1	ID	Date	_	Month	Count
2	3	1/30/2012	_	Jan	1
3	6	2/1/2012	_	Feb	2
4	6	2/3/2012	_	Mar	2
5	8	2/25/2012	_	Apr	3
6	1	3/24/2012	_	May	0
7	6	3/24/2012	_	Jun	0
8	4	4/8/2012	_	Jul	2
9	2	4/24/2012	_	Aug	1
10	6	4/27/2012	_	Sep	4
11	1	7/13/2012	_	Oct	0
12	6	7/13/2012	_	Nov	1
13	6	7/19/2012	_	Dec	1
14	1	8/11/2012	_	_	_
15	7	9/1/2012	_	_	_
16	1	9/14/2012	_	_	_
17	5	9/23/2012	_	_	_
18	2	9/29/2012	_	_	_
19	7	11/29/2012	_	_	_
20	5	12/14/2012	_	_	_
Sheet1

This array formula** entered in E2 and copied down:

=SUM(IF(FREQUENCY(IF(TEXT(B$2:B$20,"mmm")=D2,MATCH(A$2:A$20,A$2:A$20,0)),ROW(A$2:A$20)-ROW(A$2)),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.

I'm assuming the dates are all within the same year.