EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Excel Excel Resources Maths Function in Excel SUMIF Formula in Excel
 

SUMIF Formula in Excel

Madhuri Thakur
Article byMadhuri Thakur

Updated May 9, 2023

SUMIF Formula in Excel

 

 

SUMIF Formula in Excel

In this article, we will learn about the SUMIF formula in Excel. We will also look into the use of the function with several examples.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

The sum function adds cells or all cells in a range. The SUMIF function adds cells or ranges when a specific criterion is met. Thus, this function adds all the cells that fulfill a certain condition or criteria. A criterion could be a number, text, or logical operator. In short, it is a conditional sum of cells in a range.

This function is a worksheet function and is available in all versions of Microsoft Excel. It is categorized under Math & Trigonometry formulas.

The formula is written as below:

How to Use SUMIF Formula in Excel?

SUMIF Formula

The syntax has arguments, as mentioned below:

  • Range – It is a required argument. It is the cell range on which the condition/criteria are applied. The cell should contain numbers or text, dates, and cell references that have numbers. If the cell is blank, it will be ignored.
  • Criteria – It is a required argument. It is the criteria that will decide which cells to be added. In other words, it is the condition based on which the cells will be summed up. The criteria can be text, number, dates in Excel format, cell references, or logical operators. It could be like 6, “yellow”, C12,” <5″, 05/12/2016, etc.
  • Sum_ range – The cell range needs to be added. It is an optional argument. They are cells other than those found in range. If there is no sum range, the original cell range is added.

How to Use SUMIF Formula in Excel?

The SUMIF formula in Excel is very simple and easy to use. Let’s start with a very simple example of SUMIF. In this example, we only have one column or range.

You can download this SUMIF Formula Excel Template here – SUMIF Formula Excel Template

Example #1

This is a very basic example with only two arguments.

Below is the single column data on which we will use SUMIF. We need to add cells that are above or >20 in the cell range A1: A8.

Sumif Formula Example 1.1

We will now write the formula in cell A9 as below:

Sumif Formula Example 1.2

In this, the range is “A1: A8”, the criteria are “>20”, and then since there is no sum_range so the cells of range (A1: A8) will be added.

Press Enter to get the result.

Sumif Formula Example 1.3

All cells above 20 are added in the range. Only cell A3 which has 15, and cell A5 which has 10, are excluded.

Also, we can use a cell reference as a criterion instead of hard-coding the actual value.

Sumif Formula Example 1.4

We have used E4 and E5 as cell references instead of 20 and 40. Now press Enter to see the result.

Sumif Formula Example 1.5

This is how we use cell references as criteria instead of writing them.

Example #2

This example shows a data set of different fruits and their stocks, as shown below. Suppose we want to know the total stock of mangos out of all fruits.

Sumif Formula Example 2.1

We will now write the formula in cell E3:

Sumif Formula Example 2.2

The arguments in the formula are explained below:

Range: A2: A11. In this range, the criteria of mangos will be applied.

Criteria: It is mangos, as we want to know the sum of mangos from all fruits.

Sum_range: The sum range here is B2: B11.

Press Enter to get the result.

Sumif Formula Example 2.3

We have 450 mangos out of all fruits.

Example #3

We have sales data from different zones. Let’s say we want to know the total sales for the South zone.

Sumif Formula Example 3.1

We will write the formula in cell B12 as below:

Range: A2: A10

Criteria: SOUTH

Sum_range: B2:B10

Sumif Formula Example 3.2

Press Enter.

Sumif Formula Example 3.3
So the total sales for the south zone are 197. Also, we can use a cell reference in criteria instead of south.

Sumif Formula Example 3.4
Here A3 is a cell reference used in place of “South”.Now press Enter.

Sumif Formula Example 3.5
So we can also use cell references in place of writing the criteria.

Example #4

We have a sales data table for six months. And we want to find out the total sales for May.

Sumif Formula Example 4.1
We will write the formula in cell B16.

Sumif Formula Example 4.2

Once you press Enter, you will see the Result.

Sumif Formula Example 4.3
We will now look at some different SUMIF scenarios with text criteria.

Example #5

We have sample data of balloons and their quantities.

Sumif Formula Example 5.1
We want to sum up all blue balloons, whether sky blue or navy blue, out of all balloons.
We will write the formula below:

Sumif Formula Example 5.2

Press Enter to get the result.

Sumif Formula Example 5.3
In this case, we want to sum the cell based on a partial match. Hence, all cells containing “Blue” will be summed up, single or combined with other words. So we use a wild card *.
And so Blue, sky blue, and navy blue are all summed up.
In case you want to add all balloons except for blue balloons. We will write the formula below.

total balloons except for blue balloons 1

The criteria in this example are “<>Blue”, which means add all except for blue balloons. It will also add sky blue and navy blue in this case.
Press Enter to see the total number of balloons except for blue balloons.

total balloons except for blue balloons 2

We will write this formula if we want to add all balloons except for all shades of green.

total balloons except for green balloons 1

The criteria in this formula are any cell containing the word green or in combination with other words like dark or light green will not be summed up.

total balloons except for green balloons 2
Hence, all balloons are summed up except for green, dark green, and light green balloons.

Also, we can use cell references in criteria when using logical operators.

Example #6

Taking our previous example, if we want to add all balloons except for red balloons.

total balloons except for red balloons 1
Press Enter to see the Result.

total balloons except for red balloons 2

You can use the options below if you want only a specific cell or the “= to” operator. We will write the formula in any of the ways. Suppose we only need the sum of Pink balloons.

all pink balloons 1
Or

all pink balloons 2
Both of them will give the below answer.

all pink balloons 3

Let’s now see how to add blank cells using SUMIFs. Let us say we have the above data table with blank cells and want to sum up the blank cells. We can do so by writing the below formula.

total blank cells 1
To see the sum of blank cells, press Enter.

total blank cells 2
Here the logical operator “=” is used for blank cells. The above examples would have given you a clear understanding of the function and its uses.

Things to Remember

  • To simplify things, make sure that range and sum_range are the same size.
  • If the criteria are text, it should always be written within quotes.
  • You might #VALUE! Error if the given text criteria are more than 255 characters.
  • In SUMIF, the range and sum_range should always be ranges, not arrays.
  • If you don’t provide a sum_range, the function will add cells within the range that meet your specified criteria.

Recommended Articles

This has been a guide to SUMIF Formula in Excel. We discuss using SUMIF Formula in Excel with examples and downloadable Excel templates. You may also look at these useful functions in Excel –

  1. SUMIF in Excel
  2. SUM Function in Excel
  3. Excel SUMIF with OR
  4. Sumif Function Examples

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

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

EDUCBA

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

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

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

Download SUMIF Formula Excel Template

EDUCBA Login

Forgot Password?

EDUCBA

Download SUMIF Formula Excel Template

EDUCBA

डाउनलोड SUMIF Formula Excel Template

🚀 Limited Time Offer! - 🎁 ENROLL NOW