SUMIF Function In MS Excel

You are here : Home / MS Excel tutorials for beginner to experts

By Shivani Garg

SUMIF function is used to sum cells based on one criteria. It can be used in many ways such as to sum 

• Numbers
• Text
• Dates

Let's learn with Example

How to Sum Numbers using SUMIF Function

>> SUMIF function below sums the values in the selected range that are less than selected Criteria.

For example, for sum values less than 40 , C7 equals =SUMIF(C2:C6,"<=40")

>> The SUMIF function below sums values in the range if the corresponding cells in the other range contains the criteria value number.

For example, for sum values in the range D2:D7 if the corresponding cells in the range C2:C7 contain the value 50. E6 equals =SUMIF(C2:C7,50,D2:D7)

How to Sum Text using SUMIF Function

>> The SUMIF function below sums values in the range if the corresponding cells in the other range contains the criteria Text.

For example, for sum values in the range C2:C13 if the corresponding cells in the range B2:B13 contain the text Rose exactly . D13 equals =SUMIF(B2:B13,"Rose",C2:C13)

>> The SUMIF function below sums values in the range if the corresponding cells in the other range contains the criteria Text +1 character.

For example, for sum values in the range C2:C13 if the corresponding cells in the range B2:B13 contain the text Rose +1 character . D13 equals =SUMIF(B2:B13,"Rose?",C2:C13)

A question mark (?) matches exactly one character.

>> The SUMIF function below sums values in the range if the corresponding cells in the other range contains the either criteria Text.

For example, for sum values in the range C2:C13 if the corresponding cells in the range B2:B13 contain the text Rose or Lotus exactly . 

D13 equals =SUMIF(B2:B13,"Lotus",C2:C13)+SUMIF(B2:B13,"Rose",C2:C13)

How to Sum Dates using SUMIF Function

>> The SUMIF function below sums the values in the range after the selected criteria Date

For example, for sum the Number of admissions after 13 july 2018, 

D12 equals =SUMIF(B2:B11,">"&DATE(2018,7,13),D2:D11)

DATE function in Excel accepts three arguments: year, month and day.

Likewise , Any value can be Added According to dates.

Read More on Functions :

Count Function in MS Excel     

Sum Function in MS Excel

COUNTIF Function In MS Excel

SUMIF Function In MS Excel

Also Read, 

MS Excel tutorials for you :)

Related >>

1. Range in MS Excel
2. Fibonacci Sequence in MS Excel
3. Transpose in MS Excel
4. Hide Columns or Rows in MS Excel
5. Skip Blanks in MS Excel
6. Comments in MS Excel
7. Formulas and Functions in MS Excel
8. Percent Change in MS Excel
9. Paste Options in Ms Excel
10. Status Bar in MS Excel
11. Ribbon in MS Excel
12. Templates in MS Excel
13. Find & Select in MS Excel
14. Data validation in MS Excel
15. Print in MS Excel
16. Share MS Excel Data in Word Documents
17. Share MS Excel Workbook with Multiple Users
18. Magical keyboard shortcuts in MS Excel with examples
19. Very Important  Keyboard Shortcuts in MS Excel
20. Freeze Panes in MS Excel
21. Split Workbook in MS Excel
22. Spell Check in MS Excel
23. Protect Worksheet in MS Excel
24. Protect Workbook in MS Excel
25. Lock Cells in MS Excel
26. Read Only Workbook in MS Excel
27. Mark Workbook as Final in MS Excel
28. Drop Down List in MS Excel
29. Setting Budget Limit in MS Excel
30. Prevent Duplication in MS Excel
31. Count Function in MS Excel     
32. Sum Function in MS Excel
33. COUNTIF Function In MS Excel
34. Count Characters in MS Excel
35. Running Total (Cumulative Sum) in MS Excel
36. SUMIF Function In MS Excel
37. Logical Functions in MS Excel
38. IF Function in MS Excel
39. Cell References in MS Excel
40. Create Hourly Pay Sheet using Absolute Reference in MS Excel
41. Copy Exact Formula in MS Excel

Labels: MS Excel Shivani Garg