EDUCBA

EDUCBA

MENUMENU
  • Blog
  • All Courses
  • All in One Bundle
  • Login
Home Excel Excel Resources Maths Function in Excel Sumif Function Examples

Sumif Function Examples

Pradeep S
Article byPradeep S
Madhuri Thakur
Reviewed byMadhuri Thakur

Updated May 9, 2023

Sumif Function Examples

Sumif Function Examples (Table of Contents)

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

  • Overview of Sumif Function Examples
  • How to Use SUMIF Function in Excel?

Overview of Sumif Function Examples

It adds the values or sum-up cells that meet the given criteria’ expectations.

Note: Criteria may be numeric or text or date, or expression values

The Formula for the SUMIF function is:

Sumif Function Example formula

The SUMIF function syntax or formula has the below-mentioned arguments:

  • Range: (Compulsory or required argument) It is the range or array of cells that need to be evaluated by your criteria, e.g., A1:A12
  • Criteria: (Compulsory or required argument) It indicates what needs to be added or summed up in a range of cells or which cells to add in a field.

Note:

    • In the criteria argument, values should always be presented as or enclosed within double quotes.
      • Because it is case-insensitive, you can present this criteria argument parameter in lower, proper, or upper-case text format.
  • sum_range: (Optional parameter or argument) It is an optional array or range of numeric values that must be added.

How to Use SUMIF Function in Excel?

Let’s check out how the SUMIF function works in Excel.

You can download this Sumif Function Examples Template here – Sumif Function Examples Template

Example#1 – SUMIF Function with Text Criteria

In the below-mentioned example, the table contains a list of regions in column C (C8 to C20) & corresponding sales in that region in column D (D8 to D20). Here I want the sum of sales value in the North region only, i.e., “NORTH” from this sales dataset range (C8 to C20).

Sumif Function Example 1.1

Let’s apply the SUMIF function in cell “F11” with an insert function option or type a formula, i.e., =SUMIF automatically the argument which needs to enter appears i.e.

=SUMIF (range, criteria, [sum_range])

Now the arguments need to be entered i.e.

Sumif Function Example formula

  • Range: Column A is the range or array of cells that need to be evaluated by your criteria, i.e., Here, the range is A2 to A14
  • Criteria: It is a parameter or a condition that indicates the SUMIF function in which cells in a range need to be summed or added up; here, I want the sum of sales value for the North region only; hence we need to enter text criteria, i.e., “NORTH.”
  • [sum_range]: Column B is an array or range of sales values that must be added. i.e., here, the range is B2 to B14

Sumif Function Example 1.2

Click ok after entering all three arguments.

Sumif Function Example 1.3

=SUMIF (A2:A14,D2,B2:B14)

i.e., returns the sum of sales value in the north region, i.e., “8700.”

Sumif Function Example 1.4

Example #2 – Numeric Values with Arithmetic or Comparison Operators

Various arithmetic operators can be used in the SUMIF function:

>             Greater Than

<             Less Than

=             Equal to

>=          Greater than or equal to

<=          Less than or equal to

<>          Less than or greater than

Note: All the operator (<) and the threshold amount should be enclosed in double-quotes (“) in the SUMIF function

a. SUMIF Function with Greater Than (>) operator

I have a sales value column A (A2 to A14) in the example below. So here, I want the sum of sales value which is greater than 1500, i.e., “>1500” from this sales dataset range (A2 to A14).

Greater Than (>) operator A1

Now, let’s apply the SUMIF function in the cell “D2”, i.e., =SUMIF (range, criteria); you can skip the [sum_range] argument here. 

Greater Than (>) operator A2

=SUMIF(A2: A14,”>1500″) formula sums the amounts in column D when they are greater than 1500 & returns the sum of sales value, i.e., 15331

Greater Than (>) operator A3

b. SUMIF Function with Lesser Than (<) operator

I have a sales value column I (A2 to A14) in the example below. So here, I want the sum of sales value which is lesser than 2000, i.e., “<2000” from this sales dataset range (A2 to A14).

Lesser Than (<) operator B1

Now, let’s apply the SUMIF function in the cell “D3”, i.e., =SUMIF (range, criteria); you can skip the [sum_range] argument here.

Lesser Than (<) operator B2

=SUMIF(A2: A14,” <2000″) formula sums the amounts in column D when they are lesser than 2000 & returns the sum of sales value, i.e., 15331

Lesser Than (<) operator B3

Example #3 – SUMIF Function with Wildcard Character

Suppose you want to sum up or add values for the cells containing specific text; wildcard characters will help you.

The most widely used wildcard characters in the Excel SUMIF function are:

(a) asterisk (*) To match any sequence of leading or trailing characters.

(b) Question mark (?) used to track, identify, or take a count of a single character.

SUMIF Function with Asterisk (*) Wildcard Character

SUMIF function supports wildcards like an asterisk (*) & Question mark (?). An asterisk (*) means “one or more characters.”

Suppose, in the below-mentioned example, I want to sum up OR add up the sales values for a specific text, i.e., the Apparel column containing “PANT” text in column C. Let’s check to use the SUMIF function with wildcard asterisk (*) in Excel.

In the below-mentioned example, the table contains the list of product IDs in column A (A2 to A14), apparel in column B (B2 to B14), and its sales value in column C (C2 to C14). Here I want the sum of sales value for the Apparel column containing “PANT” text.

SUMIF Function with Wildcard Character

Let’s apply the SUMIF function in cell “F6″. I.E. =SUMIF (B2:B14,”*PANT*”,C2:C14)

 The SUMIF function in the sums mentioned above OR adds up the range C2 to C14 if its corresponding or neighbor cells contain the keyword “PANT” in the range B2 to B14.

  • Range: The range of cells needs to be evaluated by your criteria, i.e., column B. It is a range of cells containing the keyword “PANT”, i.e., B2 to B14.
  • Criteria: Here, I want to match all apparel items that contain the keyword or specific text “PANT”; hence, the criteria are “*PANT*.”
Note: It must be an enclosed literal text and the wildcard in double quotes (“”)
  • [sum_range]: Column C is an array or range of sales values that must be added. i.e. C2 to C14.

Sumif Range Criteria

You can click enter after entering all three arguments in the SUMIF function.

=SUMIF (B2:B14,”*PANT*”,C2:C14)

i.e., returns the sum of sales value for the apparel “PANT”, i.e., “23486.”

sum of sales value

Recommended Articles

This is a guide to Sumif Function Examples. Here we discuss How to use SUMIF Function in Excel, examples, and downloadable Excel templates. You may also look at these useful functions in Excel –

  1. Excel SUMIF with OR
  2. SUMIF with Multiple Criteria
  3. SUMIF in Excel
  4. SUMIF Formula in Excel
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
Financial Analyst Masters Training Program
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Data Science Bundle
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Software Development Bundle
5000+ Hours of HD Videos
149 Learning Paths
1050+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
All in One Excel VBA Bundle500+ Hours of HD Videos | 15 Learning Paths | 120+ Courses | Verifiable Certificate of Completion | Lifetime Access
Financial Analyst Masters Training Program2000+ Hours of HD Videos | 43 Learning Paths | 550+ Courses | Verifiable Certificate of Completion | Lifetime Access
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

Download Sumif Function Examples Template

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download Sumif Function Examples Template

EDUCBA

डाउनलोड Sumif Function Examples Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more