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