What is wrong with my excel formula ?

Question

I am trying to sum total fuel according to vehicle number

can any one please see my file ?

http://www.freefilekey.com/link.php?ref=20080904071800TXq

thank you

Answer 1

Your formula seems fine, but you appear to have imported some strange formatting with the figures in the Fuel column, either from another operating system or program, which is not compatible with Excel and therefore which prevents Excel from recognising them.

I couldn't find a way to change this - even Clear > Formats didn't make any difference, but if you simply re-type each figure in over the existing one, your spreadsheet will work as required.

The reason numbers 31 and 45 appear in the Total column is that, strangely, the formatting of these is 'normal' i.e. different from the others and this may give you a clue to the reason behind the problem and how to avoid it in the future.

Answer 2

Your colmun B seems corrupted in some way not sure what you have done to it. I would insert a new colum and retype the figures from column b. Then add the formula

=SUMIF(A:A,A2,B:B)

Answer 3

Although the cells are now formatted as numbers, they were likely formatted as text at the time the data was entered.  

In order to change the format, go to each cell,  press F2 then enter on each one.  This will tell excel to apply the new format.

If you have too much data to do it manually like that, in an empty column, put the formula = VALUE(B2) and drag/copy down the length of your data.  Then, replace your list with the numerical version by copying the new column, right-click on the original list and select "paste special" and "values".