Excel 2007 : Counting formula

Ok, so, I am a huge novice when it comes to Excel and my skills will not allow me to figure out how to create a formula for the type of counting I'd like to do with my data. Please forgive me if I'm using incorrect terminology. Here is some information on what I'm trying to do:

I'm an educator for a non-profit that operates an outreach program for schools. I'd like to create a year-end report for the # of schools we have visited according to the county/district, type of school (public/private), and semester. My workbook is made up of 4 sheets representing spring, summer, and fall semesters as well as the year-end report on the 4th sheet.

Currently, I'm using the COUNTIFS function to count the number of schools we've been to, broken down by county/district and semester. However, because we allow a school to book us for multiple days, some schools' names appear more than once in that same column. What I would like to do is only count the first occurrence of each school name for each semester to get an accurate count. If it helps, the column range that has all the school names is D7:D164. Also, there are many blank cells in column D since we are unavailable on some days and don't allow schools to book us.

Below in bold is what I have so far. It simply counts the number of Cook county public schools outside the city of Chicago that we visited in Spring 2011. As a temporary fix, I just subtracted 5 since 3 different public schools in Cook county had multiple day visits during the spring 2011 semester-which happened 5 times:

=COUNTIFS('Spring 2011'!I7:I164, "Non-Chicago Public", 'Spring 2011'!J7:J164, "Cook")-5

I have no clue how to tell Excel that I only want it to count the first occurrence/instance of each school name. My instincts tell me I'm going to need some sort of array, but that is soooo above my head in terms of actually figuring out how to incorporate it into the counting formula or figure out a different formula all together.

Any ideas on what I need to do here?

Also, if you need more information, please let me know.

Thanks in advance for the help!!

Try this:

Please Login or Register  to view this content.

you must confirm this formula by holding the CTRL and SHIFT keys down first, then press ENTER.