Answered by:
Excel 2010 Bugs

Hello,
I have recently found that excel 2010 at times does not calculate the formula correctly. I am using a "COUNTIF" function have three cells as range. The result returned by the formula is "3" while actually the criteria appears only two times out of the three cells. There is this another error where I add one cell from each of about 10 different sheets in the work book to obtain a total. I have used the evaluate formula function and it appears that it calculates the values correclty untill the last addition, when it adds the last cell value it returns the total/final result as "0". I have edited to formula to remove few cells and add few cells but the result ends up to "0". I am worried as some of my analysis with data is already sent out to clients.
Could you please provide suggestions.
Thank you
Vinod
Question
Answers

The reason you get a 3 instead of a 2 for "REAR END" is, that cell G13 still contains the value in the background. You'll see it, when you select F11:G13 and remove the merge. You should COUNTIF only for the F column.
 Marked as answer by winodv Monday, August 1, 2011 5:11 PM
All replies

Closing and Reopening the spreadsheet fixed the sum issue. When it comes to the CountIF, this the current formula "Countif(F11:G13,j17)" , the columns F and G are MERGED. IF i change the formula to "COUNTIF(F11:F13,J17)", I am getting the correct results. While I am using the previous format of the formula at other place and getting the correct results, I wonder why this is happening just for this cell.
V 
Hi Vinod  Thanks for posting your formula, would it be possible to post the contents of the cells F11:G13 and J17? I think this has something to do with merging the cells, but want to make sure we understand all of the parameters the formula is looking at.
Thanks!
Please remember to mark this as "answer" if it resolves/answers your issue. 
Ryan the range in the Countif formula F11:G13, where the columns F and G are merged. The contents in F11 is 'Angle", and F12 & F13 is "SideSwipe". The parameter/criteria in the countif Formual is J17 which had a Value of "SideSwipe". The result should have been 2 but it returns 3. Please keep in mind that this formula works fine with other sheets which have similar formatting. For some reason this is the only sheet that is showing a incorrect value.
Thank you for looking in to this.
Vinod
V 
Hi
I must be honest I cannot reproduce the issue you have reported using the information you have provided.
Can you find an empty part of your sheet and enter the formula
=F11
then copy this down two rows and across to the next column. This way we can see exactly what is in the range F11:G13
I'd expect you to get
Angle 0
SideSwipe 0
SideSwipe 0
However the comments posted suggest otherwise.
G North MCT 
Hello North,
I have encountered this error in one of the sheets. I have deleted other sheets and a copy of the sheet in discussinon can be downloaded from our FTP using the following link.
The cell in discussion is J19. Please note that this link will be valid only for 2 days.
https://gmb.egnyte.com/hs/20110801/af97bf3ac33549c2
Thank you
Vinod
V 


The reason you get a 3 instead of a 2 for "REAR END" is, that cell G13 still contains the value in the background. You'll see it, when you select F11:G13 and remove the merge. You should COUNTIF only for the F column.
 Marked as answer by winodv Monday, August 1, 2011 5:11 PM



It looks like we've got the answer/workaround....wondering, winodv, how are the values being inserted into these cells and then merged? Like Alexander said, when doing it thru the UI it should pop up a message on merging and tell you G13 is being deleted. Is any of this being done by code?
Please remember to mark this as "answer" if it resolves/answers your issue. 
All the data is being typed manually Ryan, I encountered few more scenarios today. I unmerged the cells and found that the two cells that were previously merged had the same data after un merging them.
Again all the data is manually typed in.
The cells are mergerd before entering the data. If that answers your question
Thank you
V 
On Mon, 1 Aug 2011 20:13:58 +0000, winodv wrote:>>>All the data is being typed manually Ryan, I encountered few more scenarios today. I unmerged the cells and found that the two cells that were previously merged had the same data after un merging them.>>Again all the data is manually typed in.>>>>The cells are mergerd before entering the data. If that answers your question>>Thank you>VI think HansV, an MVP in another group, has produced a method to reproduce the problem  and I can confirm that the problem occurs in Excel 2007 also.If the Merge Cells is applied using the format painter, or by copying a merged cell and then doing a Paste Special/Format over the unmerged cells, rather than using the Merge Cells command, the data in the right hand cell is preserved.
Ron 

I would think copying a format should be the same as applying it. But it's not in the case of Merge cells, at least for 2007 and 2010 on our respective machines.
One could argue that you don't need that message since you will not be losing any data! So perhaps the real question is why does copy format work differently with merge cells, than does applying the merge directly?
Ron 
I've also found a bug :
When I use sum function and search the rang with rang or equation.rang, the result is not valid.
See the linked file function_sum_rang_bug
To avoid this problem, i have used the function round()