Unique Values based on multiple criteria (w/dates)

J

JohnDozer

New Member
Joined
Jun 18, 2011
Messages
6
Hello,

I've spent the last two days trying to figure this out. I've come across many forumlas but they are so confusing that I can't tweak them to suit my needs.

I need to count the unique values of names based on the month of a date and the text in another column. Data sample below

For example:
Count # of unique NAMES for January with MGR Balzer: 4

Any help would be appreciated. THANKS!

NAME DATE MGR
Anderson, Joshua 1/30/11 Balzer
Anderson, Joshua 1/2/11 Balzer
Custodio, Sarah 1/30/11 Balzer
Custodio, Sarah 1/23/11 Balzer
Reaves, Tampa 1/30/11 Balzer
Reaves, Tampa 1/23/11 Balzer
Vrabac, Adisa 1/30/11 Balzer
Brown, Richard 1/30/11 O Neal
Brown, Richard 1/23/11 O Neal
Freeman, Jeffery 1/30/11 Dozerstein
Freeman, Jeffery 1/16/11 Dozerstein
Freeman, Jeffery 1/9/11 Dozerstein
Freeman, Jeffery 1/2/11 Dozerstein
Anderson, Joshua 2/27/11 Balzer
Anderson, Joshua 2/20/11 Balzer
Custodio, Sarah 2/20/11 Balzer
Custodio, Sarah 2/13/11 Balzer
Custodio, Sarah 2/6/11 Balzer
Jackson, Harriet 2/27/11 Balzer
Jackson, Harriet 2/13/11 Balzer
Jackson, Harriet 2/6/11 Balzer
Perry, Swift 2/27/11 Balzer
Perry, Swift 2/20/11 Balzer
Perry, Swift 2/13/11 Balzer
Perry, Swift 2/6/11 Balzer
Reaves, Tampa 2/27/11 Balzer
Vrabac, Adisa 2/6/11 Balzer
Brown, Richard 2/27/11 O Neal
Brown, Richard 2/6/11 O Neal
Dozer, Ajdin 2/27/11 O Neal
Dozer, Ajdin 2/6/11 O Neal
Leonard, William 2/27/11 O Neal
Leonard, William 2/6/11 O Neal
Bodway, Samantha 2/27/11 Crysis
Solo, Ernie 2/27/11 Crysis
Solo, Ernie 2/20/11 Crysis
Solo, Ernie 2/13/11 Crysis
Solo, Ernie 2/6/11 Crysis
Brown-Smith, Tatro 2/27/11 Dozerstein
Brown-Smith, Tatro 2/20/11 Dozerstein
Freeman, Jeffery 2/27/11 Dozerstein
Freeman, Jeffery 2/6/11 Dozerstein
Anderson, Joshua 3/27/11 Balzer
Anderson, Joshua 3/6/11 Balzer
Custodio, Sarah 3/27/11 Balzer
Custodio, Sarah 3/6/11 Balzer
Cltio, Shirley 3/27/11 Balzer
Cltio, Shirley 3/6/11 Balzer
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Sort by date Sort by votes
JohnDozer said:
Hello,

I've spent the last two days trying to figure this out. I've come across many forumlas but they are so confusing that I can't tweak them to suit my needs.

I need to count the unique values of names based on the month of a date and the text in another column. Data sample below

For example:
Count # of unique NAMES for January with MGR Balzer: 4

Any help would be appreciated. THANKS!

NAME DATE MGR
Anderson, Joshua 1/30/11 Balzer
Anderson, Joshua 1/2/11 Balzer
Custodio, Sarah 1/30/11 Balzer
Custodio, Sarah 1/23/11 Balzer
Reaves, Tampa 1/30/11 Balzer
Reaves, Tampa 1/23/11 Balzer
Vrabac, Adisa 1/30/11 Balzer
Brown, Richard 1/30/11 O Neal
Brown, Richard 1/23/11 O Neal
Freeman, Jeffery 1/30/11 Dozerstein
Freeman, Jeffery 1/16/11 Dozerstein
Freeman, Jeffery 1/9/11 Dozerstein
Freeman, Jeffery 1/2/11 Dozerstein
Anderson, Joshua 2/27/11 Balzer
Anderson, Joshua 2/20/11 Balzer
Custodio, Sarah 2/20/11 Balzer
Custodio, Sarah 2/13/11 Balzer
Custodio, Sarah 2/6/11 Balzer
Jackson, Harriet 2/27/11 Balzer
Jackson, Harriet 2/13/11 Balzer
Jackson, Harriet 2/6/11 Balzer
Perry, Swift 2/27/11 Balzer
Perry, Swift 2/20/11 Balzer
Perry, Swift 2/13/11 Balzer
Perry, Swift 2/6/11 Balzer
Reaves, Tampa 2/27/11 Balzer
Vrabac, Adisa 2/6/11 Balzer
Brown, Richard 2/27/11 O Neal
Brown, Richard 2/6/11 O Neal
Dozer, Ajdin 2/27/11 O Neal
Dozer, Ajdin 2/6/11 O Neal
Leonard, William 2/27/11 O Neal
Leonard, William 2/6/11 O Neal
Bodway, Samantha 2/27/11 Crysis
Solo, Ernie 2/27/11 Crysis
Solo, Ernie 2/20/11 Crysis
Solo, Ernie 2/13/11 Crysis
Solo, Ernie 2/6/11 Crysis
Brown-Smith, Tatro 2/27/11 Dozerstein
Brown-Smith, Tatro 2/20/11 Dozerstein
Freeman, Jeffery 2/27/11 Dozerstein
Freeman, Jeffery 2/6/11 Dozerstein
Anderson, Joshua 3/27/11 Balzer
Anderson, Joshua 3/6/11 Balzer
Custodio, Sarah 3/27/11 Balzer
Custodio, Sarah 3/6/11 Balzer
Cltio, Shirley 3/27/11 Balzer
Cltio, Shirley 3/6/11 Balzer
Click to expand...
Are the dates all within the same year?
 
Upvote 0
JohnDozer said:
Yes
Click to expand...
Try this array formula**:

=SUM(IF(FREQUENCY(IF(C2:C49="Balzer",IF(MONTH(B2:B49)=1,MATCH(A2:A49,A2:A49,0))),ROW(A2:A49)-ROW(A2)+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.

Assumes no empty cells within the data range.
 
Upvote 0
T. Valko said:
Try this array formula**:

=SUM(IF(FREQUENCY(IF(C2:C49="Balzer",IF(MONTH(B2:B49)=1,MATCH(A2:A49,A2:A49,0))),ROW(A2:A49)-ROW(A2)+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.

Assumes no empty cells within the data range.
Click to expand...


Wow...It worked ok so let me try to understand this beast of a formula....

So it's two if statements inside a frequency inside an if statement inside a sum.

What the match in the inside IF statement for? I don't understand how match works in conjunction with an array formula.

The ROW() - ROW(A2)+1 is an offset for the heading correct?
 
Upvote 0
JohnDozer said:
Wow...It worked ok so let me try to understand this beast of a formula....

So it's two if statements inside a frequency inside an if statement inside a sum.

What the match in the inside IF statement for? I don't understand how match works in conjunction with an array formula.

The ROW() - ROW(A2)+1 is an offset for the heading correct?
Click to expand...
Sort of. ROW() - ROW(A2)+1 returns an array of numbers from 1 to n that are used as the bins argument in the FREQUENCY function.

MATCH returns numbers from 1 to n and is the data_array argument of the FREQUENCY function.

FREQUENCY performs a series of "count ifs" based on the data_array numbers (results of the MATCH function) and the bins_array numbers(ROW() - ROW(A2)+1).

For each frequency "count if" that is greater than 0 we add 1. Then SUM adds up all the 1s to arrive at the final result which is a count.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Return multiple dates if coulmn header meets values in another cell
Replies
3
Views
400
Alex Blakenburg
A
L
  • Solved
SUMIFS based on multiple criteria and between date
2
Replies
17
Views
945
etaf
etaf
J
  • Solved
Unique Names based on multiple criteria
Replies
6
Views
611
Peter_SSs
Peter_SSs
A
  • Question Question
Is there a way to find Mx date based on multiple values in cell
Replies
0
Views
439
andyka
A
G
  • Solved
Sort/filter formula used for search formula combobox issue with shorter named values, Formula help for search combobox
Replies
6
Views
838
Akuini
Akuini

Forum statistics

Threads
1,224,586
Messages
6,179,728
Members
452,939
Latest member
WCrawford

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top