Summarize cell contents on the basis of conditions(Excel 2003, 2007, 2010)

             If you need to calculate the sum for elements based on a condition, not all values of the selected area will be taken into account. The picked values will depend on the conditions set as a range of values in other cells.

             Excel offers several options and functions for conditional summation. Your choice depends on the type of conditions. For each relevant value of a fixed use the function 'SUM IF'. In contrast to set variable conditions, you need an array formula or function 'SUM PRODUCT'. Following example illustrates the differences. Based on the data in the cells 'A1' to 'A10' and the condition set, date from cells 'B1' to 'C10' would be calculated upon.
MULTIPLE FIXED TERMS: If a value in column 'A' matches the set condition, then corresponding value from column 'B' will be summed. For the given data in Column A, every value greater than 2001 will be picked up and summed from column B, using the following formula =SUMIF(A2:A10,">2005",B2:B10) In Excel 2007, the function'SUMIFS' can accept a maximum of 127 conditions. However, you need to keep a tab on the order of arguments. Here the first range is the summing area, from where the value will be calculated. For this value to be achieved, the conditions mentioned in the other ranges have to be fulfilled. For example, the formula adds up all values in column 'A', for which the corresponding value of the column 'B' is greater than '10' and the corresponding values of the column 'C' is less than '100'. Using the formula =SUMIFS(A2:A10,B2:B10,">10", C2:C10,"<50")
VARIABLE CRITERIA: If the conditions for the sum of are not based on fi xed values, but relative to a set of cell ranges, data is considered by multiplying by a logic value. For this you need to use the 'SUMPRODUCT' function. According to the formula =SUMPRODUCT(A13:A17, 1*(B13:B17>C13:C17)), the values in column 'A' will be added only if the corresponding value in column 'B' is greater than the value in column 'C'. The important thing is to multiply the second argument with "1" to convert the logic value to a number.