SUMPRODUCT - Giving me trouble
 

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

SUMPRODUCT - Giving me trouble
 

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?

SUMPRODUCT - Giving me trouble
 

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

Thank you so much!

SUMPRODUCT - Giving me trouble
 

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