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 spreadsheetNext: insert nd delete row in protect sheet