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

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


In article ,
porter444 wrote:

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?

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

Thank you so much!

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


"porter444" wrote:

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