Count Unique Values with Multiple Conditions

S

stbrooks13

New Member
Joined
Dec 9, 2014
Messages
40
I need to count the number of unique offices by summary position=Associate, fiscal year=2014, offer acceptance date>0 and school=I1 (which is a drop down box for all the schools we recruit from). Please help! I've been scouring the web for hours. My ranges are named, I am able to come up with a value for one condition (summary position), but I don't know how to add more IF conditions:

=SUM(IF(FREQUENCY(IF(SummaryPosition="Associate",MATCH(Office,Office,0)),ROW(Office)-ROW($E$2)+1),1))
 
Sort by date Sort by votes
Thanks everyone! I tried the following formula but it resulted in 0.

=SUM(IF(FREQUENCY(IF(SummaryPosition="Associate",IF(FiscalYear="2014",IF(OfferAcceptanceDate,">0",IF(School=I1,MATCH(Office,Office,0))))),IF(SummaryPosition="Associate",IF(FiscalYear="2014",IF(OfferAcceptanceDate,">0",IF(School=I1,MATCH(Office,Office,0))))))>0,1))

Here's a sample of my data. The formula is in a field on another worksheet that references I1 as the school.

[TABLE="width: 878"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]DHG Fiscal Year[/TD]
[TD]Physical Location[/TD]
[TD]Summary Position[/TD]
[TD]School for Stats[/TD]
[TD]CAM - Offer Accepted[/TD]
[/TR]
[TR]
[TD="align: right"]2015[/TD]
[TD]High Point, NC[/TD]
[TD]Associate[/TD]
[TD]High Point University[/TD]
[TD="align: right"]3/25/2014[/TD]
[/TR]
[TR]
[TD="align: right"]2014[/TD]
[TD]High Point, NC[/TD]
[TD]Intern[/TD]
[TD]University of North Carolina Greensboro[/TD]
[TD="align: right"]11/20/2013[/TD]
[/TR]
[TR]
[TD="align: right"]2014[/TD]
[TD]Atlanta, GA[/TD]
[TD]Associate[/TD]
[TD]University of Georgia[/TD]
[TD="align: right"]11/6/2013[/TD]
[/TR]
[TR]
[TD="align: right"]2015[/TD]
[TD]Atlanta, GA[/TD]
[TD]Associate[/TD]
[TD]University of Georgia[/TD]
[TD="align: right"]7/22/2014[/TD]
[/TR]
[TR]
[TD="align: right"]2015[/TD]
[TD]Memphis, TN[/TD]
[TD]Associate[/TD]
[TD]University of Memphis[/TD]
[TD="align: right"]5/7/2014[/TD]
[/TR]
[TR]
[TD="align: right"]2014[/TD]
[TD]Memphis, TN[/TD]
[TD]Associate[/TD]
[TD]University of Mississippi[/TD]
[TD="align: right"]3/13/2013[/TD]
[/TR]
[TR]
[TD="align: right"]2014[/TD]
[TD]Charlotte, NC – SouthPark[/TD]
[TD]Associate[/TD]
[TD]North Carolina State University[/TD]
[TD="align: right"]11/16/2012[/TD]
[/TR]
[TR]
[TD="align: right"]2014[/TD]
[TD]Tysons, VA[/TD]
[TD]Associate[/TD]
[TD]George Mason University[/TD]
[TD="align: right"]4/15/2013[/TD]
[/TR]
[TR]
[TD="align: right"]2013[/TD]
[TD]Tysons, VA[/TD]
[TD]Intern[/TD]
[TD]George Mason University[/TD]
[TD="align: right"]12/17/2012[/TD]
[/TR]
[TR]
[TD="align: right"]2014[/TD]
[TD]Memphis, TN[/TD]
[TD]Associate[/TD]
[TD]University of Mississippi[/TD]
[TD="align: right"]3/18/2013[/TD]
[/TR]
[TR]
[TD="align: right"]2015[/TD]
[TD]Greenville, SC[/TD]
[TD]Associate[/TD]
[TD]Clemson University[/TD]
[TD="align: right"]11/14/2013[/TD]
[/TR]
[TR]
[TD="align: right"]2014[/TD]
[TD]Atlanta, GA[/TD]
[TD]Associate[/TD]
[TD]University of Georgia[/TD]
[TD="align: right"]11/1/2012[/TD]
[/TR]
[TR]
[TD="align: right"]2015[/TD]
[TD]Tysons, VA[/TD]
[TD]Associate[/TD]
[TD]Marymount University[/TD]
[TD="align: right"]4/30/2013[/TD]
[/TR]
[TR]
[TD="align: right"]2013[/TD]
[TD]Tysons, VA[/TD]
[TD]Intern[/TD]
[TD]Marymount University[/TD]
[TD="align: right"]11/30/2012[/TD]
[/TR]
[TR]
[TD="align: right"]2014[/TD]
[TD]Tysons, VA[/TD]
[TD]Intern[/TD]
[TD]Marymount University[/TD]
[TD="align: right"]4/30/2013[/TD]
[/TR]
[TR]
[TD="align: right"]2013[/TD]
[TD]Tysons, VA[/TD]
[TD]Intern[/TD]
[TD]Marymount University[/TD]
[TD="align: right"]11/30/2012[/TD]
[/TR]
[TR]
[TD="align: right"]2013[/TD]
[TD]Roanoke, VA[/TD]
[TD]Intern[/TD]
[TD]Virginia Tech[/TD]
[TD="align: right"]11/6/2012[/TD]
[/TR]
[TR]
[TD="align: right"]2014[/TD]
[TD]Roanoke, VA[/TD]
[TD]Associate[/TD]
[TD]Virginia Tech[/TD]
[TD="align: right"]8/28/2013[/TD]
[/TR]
[TR]
[TD="align: right"]2013[/TD]
[TD]Roanoke, VA[/TD]
[TD]Intern[/TD]
[TD]Virginia Tech[/TD]
[TD="align: right"]5/21/2013[/TD]
[/TR]
[TR]
[TD="align: right"]2014[/TD]
[TD]Fort Worth, TX[/TD]
[TD]Associate[/TD]
[TD]Texas Christian University[/TD]
[TD="align: right"]3/13/2013[/TD]
[/TR]
[TR]
[TD="align: right"]2014[/TD]
[TD]Birmingham, AL[/TD]
[TD]Associate[/TD]
[TD]University of Alabama[/TD]
[TD="align: right"]12/19/2012[/TD]
[/TR]
[TR]
[TD="align: right"]2015[/TD]
[TD]Rockville, MD[/TD]
[TD]Associate[/TD]
[TD]Virginia Tech[/TD]
[TD="align: right"]8/5/2013[/TD]
[/TR]
[TR]
[TD="align: right"]2013[/TD]
[TD]Rockville, MD[/TD]
[TD]Intern[/TD]
[TD]Virginia Tech[/TD]
[TD="align: right"]2/26/2013[/TD]
[/TR]
[TR]
[TD="align: right"]2015[/TD]
[TD]Norfolk, VA[/TD]
[TD]Associate[/TD]
[TD]Old Dominion University[/TD]
[TD="align: right"]4/29/2014[/TD]
[/TR]
[TR]
[TD="align: right"]2014[/TD]
[TD]Norfolk, VA[/TD]
[TD]Intern[/TD]
[TD]Old Dominion University[/TD]
[TD="align: right"]9/2/2014[/TD]
[/TR]
[TR]
[TD="align: right"]2014[/TD]
[TD]Norfolk, VA[/TD]
[TD]Intern[/TD]
[TD]Old Dominion University[/TD]
[TD="align: right"]1/2/2014[/TD]
[/TR]
[TR]
[TD="align: right"]2014[/TD]
[TD]Tysons, VA[/TD]
[TD]Associate[/TD]
[TD]University of Maryland[/TD]
[TD="align: right"]10/10/2013[/TD]
[/TR]
[TR]
[TD="align: right"]2013[/TD]
[TD]Memphis, TN[/TD]
[TD]Intern[/TD]
[TD]University of Tennessee[/TD]
[TD="align: right"]10/31/2012[/TD]
[/TR]
[TR]
[TD="align: right"]2015[/TD]
[TD]Memphis, TN[/TD]
[TD]Associate[/TD]
[TD]University of Tennessee[/TD]
[TD="align: right"]4/15/2014[/TD]
[/TR]
[TR]
[TD="align: right"]2015[/TD]
[TD]Memphis, TN[/TD]
[TD]Associate[/TD]
[TD]University of Mississippi[/TD]
[TD="align: right"]6/30/2014[/TD]
[/TR]
[TR]
[TD="align: right"]2014[/TD]
[TD]Rockville, MD[/TD]
[TD]Associate[/TD]
[TD]University of Maryland[/TD]
[TD="align: right"]11/2/2013[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
@stbrook13
You are tring the wrong formula. Try the formula on post #10!!
Let me know!
 
Upvote 0
Its meant to be 2014 not "2014"

Also what does 'The formula is in a field on another worksheet that references I1 as the school' mean??
 
Upvote 0
You do not have a named range 'OfferAcceptanceDate'. It is called 'OfferAccepted'. Thats why you are getting the name error.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Index Match with multiple conditions including dates
Replies
1
Views
261
StephenCrump
S
A
  • Solved
Count unique values based on critera with match in named range
Replies
3
Views
311
Fluff
Fluff
D
  • Solved
Conditional Formatting Finding Multiple Matched Values
Replies
6
Views
715
Joe4
Joe4
Skrej
  • Solved
All instances of a value tied to a specific entry across multiple sheets
Replies
7
Views
781
Fluff
Fluff
D
  • Solved
Count total award value per unique supplier
Replies
4
Views
215
Fluff
Fluff

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