Sumproduct #NUM error

[mlouise]

Help! I am a self-taught Excel beginner. I am using the following equation:

=SUMPRODUCT(--(Expenses!$C:$C='Monthly Totals'!$B4),--(Expenses!$F:$F='Monthly Totals'!$C$3),Expenses!$H:$H)

In this form it returns a #NUM error. If I modify it to this second version:

=SUMPRODUCT(--(Expenses!$C$1:$C$65535='Monthly Totals'!$B4),--(Expenses!$F$1:$F$65535='Monthly Totals'!$C$3),Expenses!$H$1:$H$65535)

it works properly, but when I go to view a different worksheet the equation reverts back to the first version and returns the #NUM error. This happens every time I switch to a different worksheet.

Is there any easy fix or explanation? Thanks!

 

[mikerickson]

SUMPRODUCT requires explicit ranges instead of whole columns, E1:E1000 rather than E:E. (Or larger if your data needs it.)

=SUMPRODUCT(--(Expenses!$C1:$C1000='Monthly Totals'!$B4),--(Expenses!$F1:$F1000='Monthly Totals'!$C$3),Expenses!$H1:$H1000)

 

[mlouise]

Yes, it works when I use the explicit range. My problem is that when I view a different worksheet in the same workbook that equation somehow loses the explicit ranges and returns the #NUM error again.