Result shows correct answer but cell shows 0

[Lise]

Hi everyone

I have been using the following formula
=SUMPRODUCT(($D$2:$D$576=T39)*($P$2:$P$576="Yes"))

which has been working well on all other columns ie M instead of P however
using P the result shows as 2 (when you step through it) which is correct but
when you hit enter the cell goes back to 0 - very strange - have checked
format etc and all looks ok. any thoughts on this one please?

 

[Dave Peterson]

I'm not sure why you're seeing what you're seeing when you step through
evaluating the formula.

But I'd guess that your data isn't what you expect.

Checking the format of a cell isn't enough.

You can format a cell as General (or a number) and enter ="2" and the result
will look like the number 2, but it's actually text.

If you're checking numbers, I'd compare the data comparing =count() and
counta().

=count()
will return the count of numbers in the range

=counta()
will return the count of everything in the range

If you expected was all numbers, then these should be equal.

If the problem is with the "yes" field, then I'd look for extra characters in
that data.

Using
=len(p2)
may help you find the problem.