Prev: bullet formatting in a spreadsheet
Next: insert nd delete row in protect sheet
From: Lise on 1 Mar 2010 23:36
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?
--
Thanks as always

Lise
From: Dave Peterson on 2 Mar 2010 08:23
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.

Lise wrote:
>
> 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?
> --
> Thanks as always
>
> Lise

--

Dave Peterson
 | 
Pages: 1
Prev: bullet formatting in a spreadsheet
Next: insert nd delete row in protect sheet