Auto sum error

Question

Hi,

I faced a strange problem in the excel sheet, I was working with grading of around 450+ students  and during their totalling I used auto-sum function to total the marks of 1st student, then dragged the cell down to the last candidate. But to my surprise the adjacent column which was on left to the "total" column behaved abnormally i.e it randomly added some rows and did not on other rows thereby causing a huge error in the grading. We came to know about the error only after complaints kept pouring in, then we looked back in excel sheet and saw that all students marks as a whole were not added due to the last column misbehaving. Randomly some rows were properly totalled and some were not due to which we had to face huge trouble, and also because we had blind faith in excel due to which we did not crosscheck it manually.

I would be glad if some one can explain the fault, as to why it happened.

Thanks in advance.

Sushant S. C

Answer 1

The problem is that many of the entries in J (from row 48 down) are not numbers but text.

Example J48 looks like 19 but is actually (space)19

See screenshot

best wishes

Answer 2

Dear Sushant Chopdekar,

In future, in addition to the methods which Bernard Liengme has suggested, use the following formulas on the ranges which you calculate.

=COUNTA(A1:A10)  will count numbers and text

=COUNT(A1:A10) will count number.

Totals should be equal

You could also use

=COUNTA(A1:A10)-COUNT(A1:A10)

Result should be zero

Change the cell ranges as required.

Do let me know if this helps.

Vijaykumar Shetye,

Spreadsheet Excellence,

Panaji, Goa, India

Answer 3

Tell me what rows are wrong

Excel cannot add alphabetic A to numbers as in several rows at bottom

if you want to assign 10 to each A then use =SUM(B4:J4)+COUNTIF(B4:J4,"A")*10

best wishes

Answer 4

Dear Sir,

Thanks for the reply.

I have uploaded the file to OneDrive and has the below link:

https://1drv.ms/x/s!Aq-OJZN6XVMnh5txzdiMq2Bo2iHY7A

I had used the auto sum function for the first row and then dragged the small cursor of the first cell to the last row of the sheet. But if you see carefully, many rows have not added their exam marks in the "total" column. I couldn't find the reason why it misbehaved in this way. But I tried to do lot of things to rectify it, such as copying the exam column to other sheet or to a word file then deleting the exam column, repasting it from the word file back, but nothing work.

Therefore I request you to please let me know where exactly it went wrong.

Thanks,

Sushant S. Chopdekar

Answer 5

It would help if you could upload sample file (with private info removed) to OneDrive or Dropbox and tell us the link

What is the formula?

best wishes