Thread: Avoid repeats over seperate tabs

I have a spreadsheet that among other things has a list of classes on one column, and the location of the class in another.

I have 2 tabs, one showing daytime classes, and one showing evening classes.

On a separate tab, I'm trying to calculate how many classes take place at each location, both during the daytime and the evening.

a simple =COUNTIF works fine, but there are some classes that take place during the daytime hours as well as the evening hours, so some classes are listed in both tabs.

For example, if I'm looking for how many classes take place in room 12 during the daytime and the evening time, and class name is in column A, and the location is listed in column B,(for both the daytime tab and the evening tab) I can use:

=COUNTIF(daytime!B:B,"room 12")+COUNTIF(evening!B:B)"room 12")

The problem is that there will be repeats since some classes that take place in room 12 will be on both the daytime tab and the evening tab.

I'm trying to figure out how I can list the total number of classes that take place at each location without counting the classes that are listed on both the evening tab and the daytime tab.

Does this make sense?

Thanks so much for the quick response

I tried the SUMPRODUCT formula and didn't get what I was looking for.

I think I'm trying to count according to column B. I just want it to count the number of unique class names in Column A (from both tabs)

In the end I'm trying to make a list that looks something like:

Room 1: 6
Room 2: 12
Room 3: 15
etc.

I'm using Excel 2003 on XP.

Thanks