Microsoft Excel - Sum Value returns 0

When I try to sum a range of cells, the value returns 0. All my cell values are in number format only

Shincy

HI
try this

The Excel COUNT function will count cells that contain numbers. Its syntax is:

    =COUNT(value1, value2,...value30). 

The arguments (e.g. value1) can be cell references, or values typed into the Excel COUNT formula.

The following Excel COUNT function example uses one argument -- a reference to cells A1:A5.

1. Enter the sample data on your worksheet
2. In cell A7, enter an Excel COUNT formula, to count the numbers in column A:   =COUNT(A1:A5)  
3. Press the Enter key, to complete the formula.
4. The result will be 3, the number of cells that contain numbers.
   Cell A1 isn't counted, because it contains text.

Note: Since dates are stored as numbers, the Excel COUNT function will include any cells that contain dates.

View the steps in a short Excel Count Functions video 
(COUNT, COUNTA, COUNTBLANK)  

Download zipped sample
Excel COUNT workbook

SUMIF Syntax

=SUMIF(range,criteria,sum_range)

=SUMIF(A1:A10,">20",B1:B10)

Which would SUM all numeric cells in the range B1:B20 where the corresponding row in A1:A10 was greater than 20. If we ommit the last optional argument (sum_range) the SUMIF would sum all cells in the range A1:A10 which are greater than 20, i.e.

=SUMIF(A1:A10,">20")

Note the criteria argument is in the form of a number, expression, or text that defines which cells will be summed. For example, criteria can be expressed as 20, "20", "=20",  ">20", "North", "N*".

Ok, so if we need to sum a range of cells where corresponding cells (on the same row) meet 2, or more conditions we can no longer use the SUMIF. The formulas we can use, in order of their efficiency, are

1) DSUM Download advanced examples of DSUM
2) SUMPRODUCT
3) SUM with and IF function nested and entered as an array formula. See Array Formulas for details

For all examples I will use the data as shown below. Where A2:E25 has been named: DataTable

DSUM

Adds the numbers in a column of a list, or database, that match criteria you specify. For example;

=DSUM(DataTable,B2,Criteria)

Would Sum all cells in B2:B25 that meet the criteria is the named range: Criteria (shown below)

The top row of the range: Criteria has exact copies of the headings in the range DataTable . The reference to cell B2 is telling the DSUM to sum the numbers in B2:B25 that meet the criteria. We could replace the reference to B2 with the text "Quantity", or the number 2 as the "Quantity" column is the second column in the table.

The criteria text "Bourbon" and "Vodka", under the criteria table heading "Description", tells DSUM that either "Bourbon" OR "Vodka" is a match. The same principle is used for the "Alcohol Content", i.e. "High" OR "Low". This is then seen by DSUM as an OR condition.

Note the repeat of the date under "Use By Date". This is needed when using more than 2 rows as the criteria as a blank cell is seen as a wildcard character. If we wanted to sum only data that lies between 2 dates, we would need have 2 "Use By Date" headings in our Criteria range and use: >7-Apr-2005 below one of these headings and <7-Jun-2005 under another. This is then seen by DSUM as an AND condition.

Download advanced examples of DSUM

SUMPRODUCT

Multiplies corresponding values in the given arrays, and returns the sum of those products=SUMPRODUCT((A3:A25="Vodka")*(C3:C25>VALUE("7-Apr-2005"))*(E3:E25="High")*(B3:B25))+SUMPRODUCT((A3:A25="Bourbon")*(C3:C25>VALUE("7-Apr-2005"))*(E3:E25="Low")*(B3:B25))

As with the first DSUM example, the above SUMPRODUCT example would sum all "Quantity" values where the corresponding "Use By Date" is greater than 7-Apr-2005, the "Description" is either "Vodka" OR "Bourbon" and the "Alcohol Content" is "High" OR "Low".

Note how range for each column of the table Start s at row 3 and not row 2. This is because the SUMPRODUCT has the result of each criteria check returned as TRUE (has a value of 1) or FALSE (has a value of 0). So, in the first row check (if we used row 2) it would look like; =SUMPRODUCT(0)*(0))*(0)*("Quantity"))+SUMPRODUCT((0)*(0)*(0)*("Quantity"))To read about this in detail, see our April edition of our free Excel Newsletter

The result of multiplying a text string is always #VALUE! This would cause the result of the SUMPRODUCT to return the #VALUE! error.

SUM and IF=SUM(IF(A2:A25="Bourbon",IF(C2:C25>VALUE("7-Apr-2005"),IF(E2:E25="Low",B2:B25)))+SUM(IF(A2:A25="Vodka",IF(C2:C25>VALUE("7-Apr-2005"),IF(E2:E25="High",B2:B25)))))

The above, does the same as the SUMPRODUCT example. However, this is an array formula and must be entered by pushing Ctrl+Shift+Enter

HI

try this

If you insert a row directly above the Excel SUM function in the previous example, the new row may not be included in the SUM. It may continue to sum cells A1:A4, and ignore A5.

To ensure that new rows are included in the total, you can use the OFFSET function with the Excel SUM function.

1. Select cell A5.
2. Enter the following formula:
      =SUM(A1:OFFSET(A5,-1,0))
3. Press the Enter key to complete the entry.
4. Insert a row above row 5
5. Type a number in cell A5, and it will be included in the total in cell A6

refer the links alsop http://www.contextures.com/xlFunctions01.html

Hi Shincy,

Try this...
  1) copy any blank cell
  2) drag mouse on you range
  3) right click select paste special select Values and Multiply

See here if the sum is correct, it means that your previous value are not number

to change them to be number go to menu > Data > Text to columns > will popup the "Converse to text to column wizard..." 
  select Delimeted > next > select tab > next > Click the column in that popup window> select General > finish

Hope this help.

Pichart Y.