SUMPRODUCT and named ranges

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?

---

Originally Posted by clayton

Yes it does. However I think I just found the problem. The cell said something like "This cell a number formatted as text with an ' before it". So I told it to format it as a number and it seems to works perfectly now

Thank you for your reply.

Clayton

Good to see, and thanks for the response.---