Situation: I have a long list of stock number is column A, in which I know there are many duplicates, and the related month in column B. I want to count the unique stock numbers in column A for each month in column B. Example:
A B
1A Jan
1A Feb
1B Feb
1A Jan
1B Feb
2C Feb
2A Jan
1C Jan
2C Feb
I would want January and February to return a count of 3. I can use {=SUM(1/COUNTIF(A1:A10,A1:A10)} to count unique values in A but cannot figure out how to add in the month qualifier.
Thanks!
Maybe like this:
=SUM(IF(FREQUENCY(IF(B1:B9="Jan",IF(A1:A9<>"",MATCH(A1:A9,A1:A9,0))),ROW(A1:A9)-ROW(A1)+1),1))
array formula
Try this...
Data Range
This array formula** entered in E1 and copied down:
=SUM(IF(FREQUENCY(IF(B$1:B$9=D1,MATCH(A$1:A$9,A$1:A$9,0)),ROW(A$1:A$9)-ROW(A$1)+1),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 MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
This worked, thank you both!
Would you also happen to know how to expand the "IF" qualifiers? For example if I had a column C that also had a year and I wanted to count only 2013?
Maybe this...
Data Range
This array formula** entered in F2:
=SUM(IF(FREQUENCY(IF($B$1:$B$9=$E2,IF($C$1:$C$9=F$1,MATCH($A$1:$A$9,$A$1:$A$9,0))),ROW(A$1:A$9)-ROW(A$1)+1),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.
Copy across to H2 then down as needed.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
bbrunof13 
Register To Reply
Bookmarks