Count Unique Values with Multiple Conditions

[stbrooks13]

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

 

[stbrooks13]

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.

DHG Fiscal Year	Physical Location	Summary Position	School for Stats	CAM - Offer Accepted
2015	High Point, NC	Associate	High Point University	3/25/2014
2014	High Point, NC	Intern	University of North Carolina Greensboro	11/20/2013
2014	Atlanta, GA	Associate	University of Georgia	11/6/2013
2015	Atlanta, GA	Associate	University of Georgia	7/22/2014
2015	Memphis, TN	Associate	University of Memphis	5/7/2014
2014	Memphis, TN	Associate	University of Mississippi	3/13/2013
2014	Charlotte, NC – SouthPark	Associate	North Carolina State University	11/16/2012
2014	Tysons, VA	Associate	George Mason University	4/15/2013
2013	Tysons, VA	Intern	George Mason University	12/17/2012
2014	Memphis, TN	Associate	University of Mississippi	3/18/2013
2015	Greenville, SC	Associate	Clemson University	11/14/2013
2014	Atlanta, GA	Associate	University of Georgia	11/1/2012
2015	Tysons, VA	Associate	Marymount University	4/30/2013
2013	Tysons, VA	Intern	Marymount University	11/30/2012
2014	Tysons, VA	Intern	Marymount University	4/30/2013
2013	Tysons, VA	Intern	Marymount University	11/30/2012
2013	Roanoke, VA	Intern	Virginia Tech	11/6/2012
2014	Roanoke, VA	Associate	Virginia Tech	8/28/2013
2013	Roanoke, VA	Intern	Virginia Tech	5/21/2013
2014	Fort Worth, TX	Associate	Texas Christian University	3/13/2013
2014	Birmingham, AL	Associate	University of Alabama	12/19/2012
2015	Rockville, MD	Associate	Virginia Tech	8/5/2013
2013	Rockville, MD	Intern	Virginia Tech	2/26/2013
2015	Norfolk, VA	Associate	Old Dominion University	4/29/2014
2014	Norfolk, VA	Intern	Old Dominion University	9/2/2014
2014	Norfolk, VA	Intern	Old Dominion University	1/2/2014
2014	Tysons, VA	Associate	University of Maryland	10/10/2013
2013	Memphis, TN	Intern	University of Tennessee	10/31/2012
2015	Memphis, TN	Associate	University of Tennessee	4/15/2014
2015	Memphis, TN	Associate	University of Mississippi	6/30/2014
2014	Rockville, MD	Associate	University of Maryland	11/2/2013

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[GerryZ]

@stbrook13
You are tring the wrong formula. Try the formula on post #10!!
Let me know!

 

[steve the fish]

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

 

[stbrooks13]

See tab 2, cell B2

https://drive.google.com/file/d/0B4fCnnVL7eHCb211MEY0MlV2VDg/view?usp=sharing

 

[steve the fish]

You do not have a named range 'OfferAcceptanceDate'. It is called 'OfferAccepted'. Thats why you are getting the name error.

 

[steve the fish]

The answer is 2. Is that as expected?

 

[stbrooks13]

GENIUS! Thank you so much!