Excel > Functions

Sum, SumIf, SumIfs functions in Excel

How to use Sum, SumIf, SumIfs function in Excel?

In the previous post, we learnt about counting logical and bit values in Excel. In this post, we shall learn about sum range of cells value in Excel.

Sum function

To sum/add a range of cells in MS Excel, we can use SUM function. SUM function can either by used by directly writing in the cell or by clicking on Sum command from the 'Editing' group under 'HOME' tab on the Ribbon.

So the formula to sum E2 to E5 columns values are '=SUM(E2:E5)'.

Sum by clicking on the Ribbon

Sum command on the ribbon

Sum by directly writing in the cell

Sum function result in excel

Here we are adding the values of E2 to E5 cells and the result is shown below.

sum function in MS Excel

SumIf function

Like CountIf function, SUMIF function is also used to sum the range of cells based on one criteria. In below example, we are adding value of E column only when the cell value is more than 350.

sumif function in ms excel

The criteria is written in double quote as 2nd parameter. Instead of passing 2nd parameter as greater than, less than or equal to, we can also put criteria based on other cell range.

For example, we want to add the E column value only when its corresponding B column value is "Ram". To do this, we have written formula as '=SUMIF(B2:B5, "Ram", E2:E5)' ie. Add the value from E2 to E5 only when B2 to B5 value contains "Ram". 

sumif range based on other column value in excel

and the result is below.

sumif we parameter in excel

SumIfs function

If our requirement is to use mutilple criteria while summing the range of cells, we can use SumIfs function.

So in below example, we want to sum the value of E column only if B column value is "Ram" and C column value is "Sita". So our formula will be '=SUMIFS(E2:E5, B2:B5, "Ram", C2:C5, "Sita")'.


  1. E2 to E5 is sum range
  2. B2:B5 is criteria 1 range
  3. "Ram" is the criteria value of B2:B5
  4. C2:C5 is criteria 2 range
  5. "Sita" is the critera value of C2:C5
Sumifs function use in MS Excel
As this criteria is met by only 1 record, so the result is below.

sumifs function result in ms excel

Similarly, we can specify more than 2 criteria also to sum range of cells.

Thanks for reading, if this helps do share to your friends and colleagues.

 Views: 310 | Post Order: 19

Write for us