Avoid repeats over seperate tabs

[tondrey]

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?

[NBVC]

Do you mean you want to count according to what class is in column A..

e.g.

=Sumproduct(--(daytime!$A$1:$A$1000=A2),--(daytime!$B$1:$B$1000="room 12"))+Sumproduct(--(evening!$A$1:$A$1000=A2),--(evening!$B$1:$B$1000="room 12"))

copied down.

Note: If you are in XL2003 or earlier can't use full column refs like A:A

and if you are in 2007, you can use Countifs()

=COUNTIFS(daytime!A:A,A2),daytime!B:B,"room 12")+COUNTIFS(evening!A:A,A2),evening!B:B,"room 12")

[tondrey]

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

[NBVC]

Hmm...

I am heading off in a couple of moments, so can't spend a lot of time... but finding unique items from 2 sheets together might not be so easy unless you somehow combine the two lists into one....

This formula will find unique counts in one sheet:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER not just ENTER...