Quote Originally Posted by clayton
I am trying to compare two conditions and count the number of cells that match the condition. It seems as though I should use SUMPRODUCT for this but if I use a named range if it then it does not see it as true. Here is what I have.

=SUMPRODUCT(($I$5:$I$67=size1)*($P$5:$P$67=0))

"size1" is a single named cell.

Example: If i have the number 5 somewhere in $I$5:$I$67 and I enter the formula like "=SUMPRODUCT(($I$5:$I$67=5)*($P$5:$P$67=0))" it counts correctly. But if I put a 5 in the named cell "size1" and type the formula like this =SUMPRODUCT((A2:A8=size1)*(B2:B8=0))" then it does not count anything.

Why will this not work?
If this won't work then is there another way of doing what I am trying to do?
All 3 versions work for me, as

=SUMPRODUCT((A2:A8=5)*(B2:B8=0))
=SUMPRODUCT((A2:A8=A1)*(B2:B8=0))
=SUMPRODUCT((A2:A8=size1)*(B2:B8=0))

If you select 'Size1' from the Name window, does it reflect the correct (single) cell?

---