Problem using SUMPRODUCT
 

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), doesnt:

=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

Problem using SUMPRODUCT
 

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"JoAnn" wrote in message
...
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