Problem using SUMPRODUCT

  • Thread starter Thread starter JoAnn
  • Start date Start date
J

JoAnn

I am having trouble replacing exact cell references with named ranges in my
formulas.

Why does the following (with exact cell references) work:

=SUMPRODUCT((DOCs!P408:P701="TF")*(DOCs!O408:O701="Y"))
Answer: 1 (which is correct)

But the following (substituting ranges for the cell references), doesn’t:

=SUMPRODUCT((W_Type="TF")*(W_New="Y"))
Generates Answer: 10 (wrong)

What am I doing wrong?

I'm running this from one sheet while the ranges are in another – both
sheets are in the same workbook. The ranges are not entire columns & they
are of the same size.

Both columns are text with currently either TF or blank in W_Type (in the
future there will be other text in there as well that I will need to find).

W_New will either be Y or blank.

Thanks,
JoAnn
 
I would venture a guess: that the naming was done incorrectly
Try pasting a list of named ranges to a blank area of your worksheet - or
just look at the list of defined names.
best wishes
 
Back
Top