SUMPRODUCT - Giving me trouble

P

porter444

I'm having trouble with the SUMPRODUCT funtion and I am hoping you can help.

I have a worksheet that has a listing of classes. In column A are the
course names and in column I are the instructors. What I want to do is
summarize how many times each instructor has delivered each course.

In a seperate worksheet I have a list of the unique course names down column
A and accross the top of the sheet I have the instructor names (B1, C1, D1...)

The formula I have tried is as follows:

=SUMPRODUCT(
--(Schedule!$A$2:$A$9999=$A2),
--(Schedule!$I$2:$I$9999=B1))

The result I get is #N/A. I have tried using CTRL+SHIFT+ENTER, but that
yields the same result.

I have used this basic formula in other workbooks and it works fine.

Any ideas why it isn't working for me now?

Thanks,

Scott
 
J

JE McGimpsey

Does your data contain #N/A in any of the values in
Schedule!$A$2:$A$9999 or Schedule!$I$2:$I$9999?
 
P

porter444

That was it! There was one row that had an #N/A in the instructor column.

Thank you so much!
 
B

Barb Reinhardt

You can build checks for ERRORS into the SUMPRODUCT. Try this:

=SUMPRODUCT(
--(if(isnumber(Schedule!$A$2:$A$9999=$A2),Schedule!$A$2:$A$9999)),
--(if(isnumber(Schedule!$I$2:$I$9999=B1),Schedule!$I$2:$I$9999)))

I haven't tested this, and I have a feeling it needs some () somewhere.
You will need to commit this with CTRL SHIFT ENTER
 
Top