Calendar and SUMPRODUCT

[sunnybiss77]

I am trying to maintain a list of classes and use the data to create a calendar. The data includes a teacher, classroom, class name, start date, and end date. I am open to manipulating the data, but ultimately want it to create a visual calendar similar to a Gannt chart and that will identify conflicts (2 teachers booked in the same room, etc.)

What I have been creating manually is great but I am hoping to automate. My manual calendar is basically:

Top row - Date for each day of the year
First column - Teacher name
When a teach is teaching a class, I use merged cells to block out the teacher's time and note the class name.

For the rooms, same concept but replace teacher name with room name.

I tried to automate this by making a list of classes and using a SUMPRODUCT formula to auto-populate the calendar. This worked okay, but takes too long to calculate. Here is my formula:

=SUMPRODUCT((TrainingEndDate>=O$1)*(TrainingStartDate<=O$1)*(Room=$A65))

O1 = Current date
A65 = Room name

Unfortunately, this calculation in so many cells takes HOURS to run.

Any ideas?

 

[Aladin Akyurek]

Try a pivot table...

 

[sunnybiss77]

I did do a pivot table, but my boss likes the gannt chart type of style, it's easier to visualize what is going on.

 

[Aladin Akyurek]

I did do a pivot table, but my boss likes the gannt chart type of style, it's easier to visualize what is going on.

If you are on 2007 or beyond, try COUNTIFS, otherwise DCOUNT(A).