SUMIF and COUNTIF in Excel
SUMIF and COUNTIF in Excel
SUMIF, SUMIFS, COUNTIF, and COUNTIFS are extremely useful and powerful for data analysis. If there was an Excel Function Hall of Fame, this family of functions ought to be included. In this article, I’ll demonstrate a bunch of different ways to use these functions, focusing mainly on all the different criteria types.
The SUMIF and COUNTIF functions allow you to conditionally sum or count cells based on a single condition, and are compatible with almost all versions of Excel:
=SUMIF(criteria_range, criteria, sum_range)
=COUNTIF(criteria_range, criteria)
The SUMIFS and COUNTIFS functions allow you to use multiple criteria, but are only available beginning with Excel 2007:
=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2,...)
=COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2,...)
AVERAGEIF and AVERAGEIFS are also part of this family of functions and have the same syntax as SUMIF and SUMIFS.
This Article (bookmarks):

SUMIF and COUNTIF Examples

Usings Dates as Criteria

SUMIFS Example: Income and Expense Report

SUMIF and COUNTIF Between Two Numbers (1<x<10)

SUMIF and COUNTIF with OR conditions

Summary Table of Criteria Types

CaseSensitive Match Using SUMPRODUCT

MAXIFS and MINIFS

Other Notes
1) SUMIF and COUNTIF Examples
We’ll start off by using a product sales table to demonstrate a few different SUMIF and COUNTIF formulas. I’ve listed a few of these examples below. To see these formulas in action and try them out yourself, you can download the example file below:
Download the Example File
(SumIfCountIf.xlsx)
Bạn đang xem: SUMIF and COUNTIF in Excel
Criteria are Text Values
Example: Sum of Sales where Category equals “student”
=SUMIF(category_range,"student",sales_range)
NOTE This formula will also match “Student” because the SUMIF family of functions are not casesensitive. You can use wildcard characters within the text string, such as “?s*” to match values where the second letter is s.
Not Equal To (<>)
Example: Sum of Sales where Model is NOT equal to “B”
=SUMIF(model_range,"<>B",sales_range)
Example: Sum of Sales where Category does NOT contain the letter “u”
=SUMIF(category_range,"<>*u*",sales_range)
Criteria is an Alphabetical Text Comparison
Example: Sum of Sales where Model is less than “C”
=SUMIF(model_range,"<C",sales_range)
Criteria is a Numeric Comparison
Example: Number of products priced over $40
=COUNTIF(price_range,">40")
NOTE When using numeric criteria, COUNTIF and SUMIF ignore text values. Numeric date values can be an exception to that (see below for date comparisons).
Criteria matches Blank Cells or Empty Cells
Example: Count of products where On Sale is blank
=COUNTIF(on_sale_range,"")
Criteria matches NonBlank Cells
Example: Number of products on sale (where On Sale is not blank)
=COUNTIF(on_sale_range,"<>")
Criteria includes a Cell Reference
Example: Sum of Sales where Price is greater than the value in cell H3
=SUMIF(price_range,">"&H3,sales_range)
Criteria is In Another Cell
Example: Sum of Sales using the criteria defined in cell K9
=SUMIF(criteria_range,K9,sales_range)
NOTE This technique is useful when you want to allow the user to choose or enter different criteria or search strings.
The cell K9 could contain a value like “student” or “40” or a criteria string such as “>40” or “<>student”
Multiple Criteria
Example: Sum of Sales where Category = “student” AND Price > 30
=SUMIFS(sales_range,category_range,"student",price_range,">30")
Example: SUMIFS between two dates
=SUMIFS(sum_range,date_range,">=1/1/2017",date_range,"<1/31/2017")
NOTE The SUMIFS, COUNTIFS, and AVERAGEIFS functions are used for multiple AND conditions. Formulas for OR conditions are a bit more tricky (see below).
2) Using Dates as Criteria
When using dates as criteria for the COUNTIF and SUMIF functions, Excel does some interesting things, depending on whether you are using “=” or “<” as the criteria and whether the dates in the criteria range are stored as numeric date values or text values.
Remember: Date values are stored in Excel as sequential numbers starting with 1 for 1/1/1900. The cell formatting may display the date in different ways, but COUNTIF and SUMIF comparisons are based on the value stored in the cell, not the way a cell is formatted. That is a good, because we normally want to compare dates and numbers without having to worry about how they are formatted.
Xem thêm: Halogen là gì? Định nghĩa, khái niệm
Criteria is a Date
=COUNTIF(criteria_range,"=3/1/17")
When using criteria such as “=3/1/17” or “Mar 1, 2017”, Excel will recognize the criteria as a date and will count all date values in the criteria_range matching that date. Excel will ALSO count recognized dates stored as text values in the criteria_range, such as “March 1, 2017” and “3/1/2017” (but not “March 1st, 2017” because Excel doesn’t recognize it as a date).
Criteria is Greater or Less than a Date
=COUNTIF(criteria_range,">3/1/17")
When using <, >, <=, or >=, Excel still recognizes the criteria as a date, but it does not convert text values in the criteria_range to date values.
Comparison to TODAY
You can use the TODAY function to make comparisons based on the current date, like this:
=SUMIF(date_range,"<"&TODAY(),sum_range)
3) SUMIFS Example: Income and Expense Report
SUMIFS is very useful in
account registers
, budgeting, and
money management
spreadsheets for summarizing expenses by category and between two dates.
The SUMIFS example below sums the Amount column with 3 criteria: (1) the Category matches “Fuel”, (2) the Date is greater than or equal to the start date, and (3) the Date is less than or equal to the end date.
=SUMIFS(amount_range, category_range, "Fuel", date_range, ">=1/1/2018", date_range, "<=1/31/2018")
The following screenshot shows an example from the download file:
4) SUMIF and COUNTIF Between Two Numbers (1 < x < 4)
COUNTIFS and SUMIFS can easily handle a condition such as 1 < x < 4 (which means x > 1 AND x < 4). However, if you are trying to make a spreadsheet compatible with older versions of Excel, you can use COUNTIF or SUMIF by subtracting the results of the condition x <= 1 from the results of the condition x < 4.
Condition  Formula using COUNTIFS 

1 < x < 4  =COUNTIFS(range,”>1″,range,”<4″) 
Condition  Formula using only COUNTIF 
1 < x < 4  =COUNTIF(range,”<4″) – COUNTIF(range,”<=1″) 
1 <= x < 4  =COUNTIF(range,”<4″) – COUNTIF(range,”<1″) 
1 < x <= 4  =COUNTIF(range,”<=4″) – COUNTIF(range,”<=1″) 
1 <= x <= 4  =COUNTIF(range,”<=4″) – COUNTIF(range,”<1″) 
A common need for these formulas is to sum values between two dates. Remember that date values are stored as numbers.
Example: SUMIF between two dates ( 1/1/2017 <= date <= 1/31/2017 )
=SUMIF(sum_range,date_range,"<=1/31/2017")SUMIF(sum_range,date_range,"<1/1/2017")
5) SUMIF and COUNTIF with OR Conditions
COUNTIFS and SUMIFS handle multiple AND conditions, but OR conditions such as X<2 OR X>3 are usually easier to handle by evaluating each condition separately and then adding the results. The two formulas below do essentially the same thing.
Condition  Formula 

x < 2 or 3 < x  =COUNTIF(range,”<2″) + COUNTIF(range,”>3″) 
x < 2 or 3 < x  =SUM(COUNTIF(range,{“<2″,”>3″})) 
To avoid doublecounting cells, the conditions must not overlap. For example, the condition “=*e*” would overlap with the condition “=yes”. The condition “<40” would overlap with the condition “>20”. If the conditions overlap, you may end up counting or adding a value twice. If there is a possibility of conditions overlapping, then you may need to use a SUMPRODUCT formula as explained below.
Use SUMPRODUCT for overlapping OR conditions
The key to avoid doublecounting is to recognize that FALSE+FALSE=0 and TRUE+FALSE=1 and TRUE+TRUE=2. This means that for a logical OR condition to be true, we can check whether the sum of two or more conditions is > 0.
Sum of Sales where Model is equal to A or B.
=SUMPRODUCT(sales,1*( ((model="A")+(model="B"))>0 ))
Sum of Sales where Model = “A” or Price > 45
=SUMPRODUCT(sales,1*( ((model="A")+(price>45))>0 ))
6) CaseSensitive SUMIF and COUNTIF
The SUMIF family does not have a casesensitive option, so we need to resort back to using array formulas or SUMPRODUCT. The FIND and EXACT functions both provide a way to do casesensitive matches.
Xem thêm: Nội dung thi Olympic Vật lý sinh viên
Sum of Sales where Category exactly matches “student” (casesensitive)
=SUMPRODUCT(sales,1*EXACT(category,"student"))
Sum of Sales where Category contains “Stu” (casesensitive)
=SUMPRODUCT(sales,1*ISNUMBER(FIND("Stu",category)))
7) MAXIF or MINIF Formula
MAXIFS and MINIFS are new Excel functions available in the latest releases (Excel for Microsoft 365, Excel 2019). Their syntax is similar to SUMIFS, allowing you to use multiple criteria.
Older versions of Excel do not have the MAXIFS or MINIFS functions, but you can use an
array formula
like this (remember to press Ctrl+Shift+Enter):
Example: Find the maximum Price where Model = “A”
(array formula) =MAX(IF(model_range="A",price_range,""))
8) Summary of Different Criteria Types
CRITERIA TYPE  EXAMPLE  MATCHES … 

Text Value  “yes” or “=yes”  “yes” or “Yes” (not casesensitive), the equal sign is optional 
Text Value with Wildcards  “=?s*”  text values where the second letter is “s” or “S” 
Alphabetical Text Comparison  “<C”  text values alphabetically less than “C” 
Equal to a Numeric Value  20 or “=20”  numeric values equal to 20 
Less than or Equal to  “<=20”  numeric values less than or equal to 20 
Greater than or Equal to  “>=20”  numeric values greater than or equal to 20 
Not Equal to  “<>0”  values not equal to the value 0 
NonBlank  “<>”  values that are not blank (formulas returning “” are not blank) 
Blank or Empty  “”  values that are blank and formulas that return “” 
Equal to a Cell Value  A42 or “=”&A42  values equal to the value in cell A42 
Comparison to a Cell Value  “>”&A42  values greater than the value in cell A42 
Equal to a Date  “=3/1/17”  date values equal to 3/1/17 as well as text values such as “3/1/17” or “Mar 1, 2017” 
< or > a Date  “>1/1/2017”  date values greater than 1/1/2017 (text values are ignored) 
Some criteria, such as casesensitive matches, may only be possible with array formulas or SUMPRODUCT.
You don’t use the functions AND, NOT, OR, ISBLANK, ISNUMBER, ISERROR, or other similar functions as criteria for SUMIF and COUNTIF. However, you can use these functions within a SUMPRODUCT formula (but that isn’t within the scope of this article).
9) Other Notes
 Comparisons are based on the value stored in the cell, not on how the cell is formatted.
 Error values in both the sum_range and criteria_range are ignored.
 SUMIFS and COUNTIFS formulas are generally faster than their SUMPRODUCT or array formula counterparts.
 The sum_range and criteria_range arguments can be references (e.g. A2:A42), named ranges or formulas that return a range (such as INDEX, OFFSET, or INDIRECT).
 Normally, you’ll want the sum_range and criteria_range to be the same length. See the documentation on the Microsoft sites (referenced below) for information about what happens when the sum_range and criteria_range are not the same length.
 The COUNTIF and SUMIF criteria can be a range (e.g. A2:A3) if you enter the formula as an array formula using Ctrl+Shift+Enter.
 The COUNTIF and SUMIF criteria can be a list such as {“>1″,”<4”}, but functions return an array containing results for the separate conditions, not a sum of both conditions (it is not the same as COUNTIFS or SUMIFS).
Some Templates that Use SUMIF

Invoice Tracker
– Uses SUMIF to create an Aging table that shows unpaid invoices past 30 days, 60 days, etc.

Account Register
– Uses a cumulative SUMIF formula to show the current account balance within the register.

Weekly Money Manager
– Uses SUMIF to show actual spending within a budget report.

Checkbook Register
– Uses SUMIF to show the current cleared balance based on whether the reconcile column contains “r” or “c”.
References

Spreadsheet Tips Workbook
– vertex42.com – by Jon Wittwer and Brent Weight

SUMIF Function
– support.office.com – The official documentation of the SUMIF function.

SUMIFS Function
– support.office.com – The official documentation of the SUMIFS function.

COUNTIF Function
– support.office.com – The official documentation of the COUNTIF function.

COUNTIFS Function
– support.office.com – The official documentation of the COUNTIFS function.

SUMIFS With Multiple Criteria and OR Logic
– at exceljet.net – Describes how the function works when you use an array for the criteria such as {“=A”,”=B”}.
Chuyên mục: Kiến thức
Comments
add one
Link
June 19, 2019, 4:28 am
nice sir
Reply
Link
November 18, 2019, 1:11 am
Hi
Kindly assist me to count ‘AA’, ‘AB’,’AC and ‘AD’ from ‘X’, ‘Y’ and ‘Z’.
Total AA, AB, AC and AD is 2500 from X, Y and Z
Regards
Reply
Jon Wittwer
Link
November 19, 2019, 10:22 am
@Saad, I’m afraid I don’t know what you mean. Are you referring to AA, AB, X, Y, etc as column labels? If you can be more specific, that would help.
Reply
Link
March 18, 2020, 5:16 am
Row A Row B
07:00:00 1
07:02:00 2
Result 07:00:00 3
How to calculate
Reply
Jon Wittwer
Link
March 18, 2020, 10:11 am
@Manju … You would need to provide more information. Please ask general questions like this via an Excel forum.
Reply
Link
August 5, 2020, 1:55 pm
I’m looking at the Money Management Template 2.1 and I see you made extensive use of the offset command in the sum command and was wonder why. For example in the balance column, SUM(M4,J5I5) seems to work just as well as your =SUM(OFFSET(M5,1,0),J5,I5)
Reply
Jon Wittwer
Link
August 27, 2020, 12:48 pm
@Randy … to make it robust to row deletion and sorting.
Reply
Link
September 19, 2020, 10:34 am
Jon,
I am try to create a spreadsheet that tracks round counts through a rifle. Similar to a running balance sheet except I am tracking over 100 rifles and need to keep each rifle on its own row and all rifles on one sheet. I am wanting it to show previous round count, new rounds shot(entered data) and then new balance or number of rounds shot. The only data that I want to enter is the number of new rounds shot that will update the previous balance along with the new balance. I keep getting a circular error. I understand why I am getting that error message but cannot figure out the formula that will give me previous and new balance on the same line. Thanks inadvance for your help!
Reply
Jon Wittwer
Link
September 22, 2020, 9:36 pm
@Steve, are you wanting to enter a new row every time that you fire shots from the rifle? Or do you just want to update the total and maintain 100 rows? If it’s the first case, you could use something very similar to the Money Management Template where each rifle is an “Account” and your Transactions worksheet could indicate the date and the number of rounds used. The Account Balance column would then keep track of the total for each individual rifle.
Reply
Link
September 28, 2020, 8:06 pm
Jon,
I am try to create a spreadsheet that tracks round counts through a rifle. Similar to a running balance sheet except I am tracking over 100 rifles and need to keep each rifle on its own row and all rifles on one sheet. I am wanting it to show previous round count, new rounds shot(entered data) and then new balance or number of rounds shot. The only data that I want to enter is the number of new rounds shot that will update the previous balance along with the new balance. I keep getting a circular error. I understand why I am getting that error message but cannot figure out the formula that will give me previous and new balance on the same line. Thanks inadvance for your help!
Reply
Leave a Comment
Your Name will be displayed along with your comment. Your Email and IP address are stored with the comment and used to identify/prevent spam (via a service provided by Automattic.com), but are not shared publicly. See our
privacy policy
to learn more.
Cancel reply