SUMIF and COUNTIF

https://www.vertex42.com/blog/excel-formulas/sumif-and-countif-in-excel.html

This workbook contains examples from the article "SUMIF and COUNTIF in Excel." Regarding copyright and s
book. You may use the ideas and techniques and formulas explained here, but you may not reproduce this wo
portions from it, just as you would not do so with a book. Thank you. - Jon Wittwer

EXAMPLES

The SUMIF and COUNTIF functions allow you to conditionally sum or count cells based on a single criteria. Th
with almost all versions of Excel. The SUMIFS and COUNTIFS functions allow you to conditionally sum or cou
multiple criteria, but are only available beginning with Excel 2007.

Criteria Type Criteria Example

Text Value "yes" or "=yes"
Text Value with Wildcards "=?s*"
Alphabetical Text Order Comparison "<C"
Equal to a Numeric Value "=20"
Less Than or Equal To "<=20"
Greater Than or Equal To ">=20"
Not Equal To "<>0"
Non-Blank "<>"
Blank or Empty ""
Equal to a Cell Value A42 or "="&A42
Equal to a Date "3/1/17"

NOTES Comparisons are based on the value stored in the cell, NOT on how the cell is formatted.

The sum_range and criteria_range arguments can be references, named ranges or formulas
INDEX, OFFSET, or INDIRECT).

The SUMIF and COUNTIF functions ignore errors in both the sum range and the criteria ran
advantages of using these functions instead of array formulas or SUMPRODUCT.

See the articles on the Microsoft sites for information about what happens when the sum_ra
the same length.

Examples of Different Criteria Types

We'll use the Product Sales Table to show examples of using SUMIF and COUNTIF with different criteria type
SUMPRODUCT are also shown.

Product Sales Table

Category Model Price Sales On Sale
Student A $25 100 y
Student B $35 200
Student C $45 400
Home A $39 1000
Home B $49 2000
Home C $59 4000 y
Business A $50 10
Business B $70 20 y
Business C $90 40

Criteria is a Text String ("=student")

Sum of Sales where Category equals "student"

Formula: =SUMIF(category,"=student",sales)
=SUMIF(category,"student",sales)

Alternate: =SUMPRODUCT(sales,1*(category="student"))

Criteria is a Text String with Wildcard Characters ("=?u*")

Sum of Sales where second letter of Category is "u"

Formula: =SUMIF(category,"?u*",sales)

Doesn't Work: =SUMPRODUCT(sales,1*(category="?u*"))

Why doesn't this work? Because logical comparisons like category="?u*" don't recognize wildc

Alternate: =SUMPRODUCT(sales,1*(MID(category,2,1)="u"))
Criteria is an Alphabetical Text Order Comparison ("<C")

Sum of Sales where Model is less than "C"

Formula: =SUMIF(model,"<C",sales)

Note: Not case-sensitive. Will ignore numeric values unless you use not equal to (like "<>M").
symbols are sorted based on their ascii or unicode values: a string beginning with "#" would be
string beginning with "A" and a string beginning with "Ω" would be greater than a string beginn

Alternate: =SUMPRODUCT(sales,1*(model<"C"))

Criteria is Not Equal To ("<>A")

Number of products where Model is NOT "A"

Formula: =COUNTIF(model,"<>A")

Alternate: =SUMPRODUCT(1*(model<>"A"))

Sum of sales where Model is NOT "A"

Formula: =SUMIF(model,"<>A",sales)

Alternate: =SUMPRODUCT(sales,1*(model<>"A"))

Sum of sales where Category does not contain "u"

Formula: =SUMIF(category,"<>*u*",sales)

Criteria is a Non-Blank Cell ("<>")

Number of products On Sale (where On Sale is not blank)

Formula: =COUNTIF(on_sale,"<>")

Note: Will also count cells that contain formulas returning the empty value "". This is one of the
a NULL function is needed.

FEEDBACK Vote for NULL Function via excel.uservoice.com

 Alternate: =SUMPRODUCT(1*(NOT(ISBLANK(on_sale))))

Criteria is Empty Cells or Blank Cells ("")

Number of products NOT On Sale (where On Sale is empty)

Formula: =COUNTIF(on_sale,"")

Note: A cell formatted to display blank, such as when the value is zero, will not necessarily be
blank or empty. Comparisons are based on the value stored in the cell, not how the cell is form

Alternate: =SUMPRODUCT(1*(on_sale=""))

Criteria is a Numeric Comparison (">=50")

Number of products priced less than $40

Formula: =COUNTIF(price,"<40")

Alternate: =SUMPRODUCT(1*(price<40))

Number of products priced greater than or equal to $50

Formula: =COUNTIF(price,">=50")

Alternate: =SUMPRODUCT(1*(price>=50))

Criteria includes a Cell Reference ("<="&A1)

Sum of Sales for products priced less than value in cell A1

Formula: =SUMIF(price,"<"&A1,sales)

Alternate: =SUMPRODUCT(sales,1*(price<A1))

Criteria is In Another Cell (A1)

Sum of Sales using the criteria for Category in cell A1

Formula: =SUMIF(category,A1,sales)
 Sum of Sales using the criteria for Price in cell A1
Formula: =SUMIF(price,A1,sales)

Multiple Criteria: Use SUMIFS for multiple AND conditions

Sum of Sales for products priced between $20 and $40.

Formula: =SUMIFS(sales,price,">=20",price,"<=40")

Multiple Criteria: Use SUMIF+SUMIF for OR conditions

Sum of sales where model is equal to A or B.

Formula: =SUMIF(model,"A",sales)+SUMIF(model,"B",sales)

Formula: =SUM(SUMIF(model,{"A","B"},sales))

Note: For this technique to work, the conditions must not overlap. For example, the condition "
overlap with the condition "=yes". The condition "<40" would overlap with the condition ">20". I
overlap, you may end up counting or adding a value twice. If there is a possibility of conditions
then you may need to use a SUMPRODUCT formula.

Multiple Criteria: Use SUMPRODUCT for OR conditions that might overlap

The key to avoid double-counting is to recognize that for a logical OR condition, TRUE+FALSE=1 and TRUE+
means that for a logical OR condition, we can check whether the sum of two or more conditions is > 0.

Sum of Sales where Model is equal to A or B.

Formula: =SUMPRODUCT(sales,1*( ((model="A")+(model="B"))>0 ))

Sum of Sales where Model = "A" or Price > 45

Formula: =SUMPRODUCT(sales,1*( ((model="A")+(price>45))>0 ))

MAX-IF Formula

Maximum Price where Model is equal to A

Formula: array formula: =MAX(IF(model_range="A",price))
 (Press Ctrl+Shift+Enter after entering an array formula)

Using Date Comparisons with COUNTIF and SUMIF

When using dates as criteria for the COUNTIF and SUMIF fuctions, Excel does some interesting things, depen
whether you are using "=" or "<" as the criteria and whether the dates in the criteria range are stored as date v
values.

Equal to a Date

The table to the right shows the date Mar 1, 2017 as a date value formatted two different
ways and also three different text values. Each of the dates are displayed differently, but
when using the criteria "=3/1/17" Excel will count them all as long date is a recognized date
format. This means that Excel is recognizing the criteria as a date and it is also converting the
date_range values to dates if it can.

Examples: =COUNTIF(date_range,"=3/1/17")
=COUNTIF(date_range,DATE(2017,3,1))
=COUNTIF(date_range,"March 1, 2017")
=COUNTIF(date_range,"Mar 1st 2017")

Less than or Greater than a Date

When using a < or > comparison, Excel converts the criteria to a date value, but it does not
convert the date_range values to date values. In the example to the right, all 5 dates are
greater than Jan 1, 2017. However, the COUNTIF function does not convert the two text
values to dates.

Formula: =COUNTIF(date_range,">1/1/2017")

Comparisons using TODAY

 Formula: =COUNTIF(date_range,"<"&TODAY())

SUMIF Between Two Dates

See the Income and Expense Report example below to see this formula in action.

Formula: =SUMIFS(sum_range,dates,">=1/1/2017",dates,"<1/31/2017")

Case-Sensitive String Comparisons Using EXACT or FIND

The SUMIF family does not have a case-sensitive option, so we need to resort back to using array formulas or
SUMPRODUCT. The FIND and EXACT functions both provide a way to do case-sensitive matches.

Sum of Sales where Category exactly matches "student" (case-sensitive)

Formula: =SUMPRODUCT(sales,1*(EXACT(category,"student")))

Sum of Sales where Category contains "Stu" (case-sensitive)

Formula: =SUMPRODUCT(sales,1*(ISNUMBER(FIND("Stu",category))))

Using COUNTIF and SUMIF for Conditions such as 1 < x < 4

Although COUNTIFS and SUMIFS can easily handle a condition such as 1 < x < 4 (which means x > 1 AND x
COUNTIF and SUMIF by subtracting the results of the condition x <= 1 from the results of the condition x < 4.

Value Condition Formula Using COUNTIF

-1 1<x<4 =COUNTIF(range,"<4") - COUNTIF(range,"<=1")
0 1 <= x < 4 =COUNTIF(range,"<4") - COUNTIF(range,"<1")
1 1 < x <= 4 =COUNTIF(range,"<=4") - COUNTIF(range,"<=1")
2 1 <= x <= 4 =COUNTIF(range,"<=4") - COUNTIF(range,"<1")
3
4 Condition Formula Using COUNTIFS
5 1<x<4 =COUNTIFS(range,">1",range,"<4")
SUMIF Between Two Dates

To sum values between (and including) two dates would be similar to the fourth example above. The following
where 1/1/2017 <= date <= 1/31/2017.

Formula: =SUMIF(sum_range,date_range,"<=1/31/2017")-SUMIF(sum_range,date_range,"<1/1/2017

Using COUNTIF and SUMIF for Conditions such as x < 2 OR x > 3

COUNTIFS and SUMIFS handle multiple AND conditions, but alone cannot necessarily handle OR conditions,
example below shows a few ways to handle this situation.

Value Formula
-1 =COUNTIF(range,"<2") + COUNTIF(range,">3")
0 =SUM(COUNTIF(range,{"<2",">3"}))
1 =COUNT(range) - COUNTIFS(range,">=2",range,"<=3")
2 CSE array formula: =SUM(COUNTIF(range,A1:A2))
3
4
5

SUMIFS Example: Income & Expense Report

SUMIFS is useful for account registers, budgeting and money tracking spreadsheets to summarize expenses
dates. The SUMIFS example below sums the Amount column with 3 criteria: (1) the Category matches "Fuel",
equal to the start date, and (3) the Date is less than or equal to the end date.

Formula: =SUMIFS(sum_range, category_range, "Fuel", date_range, ">=1/1/2018",

date_range, "<=1/31/2018")

Check Register
Date Payee Category Amount Balance
1/1/2018 Carry-Over 3,000.00 3,000.00
 1/2/2018 Auto Stop Fuel (35.42) 2,964.58
1/5/2018 Smith's Groceries (15.32) 2,949.26
1/15/2018 Albertsons Groceries (67.98) 2,881.28
1/31/2018 Paycheck Wages 2,354.00 5,235.28
2/5/2018 A Express Debt (156.23) 5,079.05
2/9/2018 ABC Insure Insurance (576.23) 4,502.82
2/15/2018 Auto Stop Fuel (24.12) 4,478.70
2/18/2018 Albertsons Groceries (76.12) 4,402.58
2/28/2018 Paycheck Wages 2,354.00 6,756.58

Income and Expense Report

Period Start 1/1/2018 2/1/2018
Period End 1/31/2018 2/28/2018
INCOME Categories
Wages 2,354.00 2,354.00 ◄ Look at the formulas used here
EXPENSE Categories
Fuel (35.42) (24.12)
Groceries (83.30) (76.12)
Savings - -
Debt - (156.23)

TEMPLATE See it in action: Account Register Template

TEMPLATE See it in action: Checkbook Register Template

TEMPLATE See it in action: Money Management Template

TEMPLATE See it in action: Weekly Money Manager

Determine if a Date is Within a List of Date Ranges

In this example, we want to know whether a date is within any of the date ranges contained in a table of start a
make the end date optional within the table, so that means that a period may be represented as just a single d
column.
 Formula: =COUNTIF(start_dates,date) + COUNTIFS(start_dates,"<="&date,
end_dates,">="&date)

Example: 30-May-2017 RESULT 1

REFERENCES

ARTICLE support.office.com: SUMIF Function

ARTICLE support.office.com: SUMIFS Function

ARTICLE support.office.com: COUNTIF Function

ARTICLE support.office.com: COUNTIFS Function

See Also https://exceljet.net/formula/sumifs-with-multiple-criteria-and-or-logic

 © 2017 Vertex42 LLC

Regarding copyright and sharing, think of this file like a

may not reproduce this worksheet or copy substantial

ased on a single criteria. These functions are compatible

to conditionally sum or count cells based on single or

Matches …
"yes" or "Yes" (not case-sensitive)
second letter is "s" or "S"
values alphabetically less than C
numeric values equal to 20
numeric values less than or equal to 20
numeric values greater than or equal to 20
values not equal to the value 0
values that are not blank (="" is considered non-blank)
values that are blank and formulas returning ""
Matches the value in cell A42
Matches "3/1/17" or "Mar 3, 2017" or date values equal to 3/1/2017

ow the cell is formatted.

named ranges or formulas that return a range (such as

range and the criteria range. This is one of the

SUMPRODUCT.

happens when the sum_range and criteria_range are not

F with different criteria types. Alternative formulas using

(="")

Result
700
700

700

Result
70

0
"?u*" don't recognize wildcard characters.

70
 Model < Result
C 3330

e not equal to (like "<>M"). Special

beginning with "#" would be less than a
reater than a string beginning with "Z".

C 3330

Result
6

Result
6660

6660

Result
7770

Result
4

y value "". This is one of the reasons why

 4

Result
6

ero, will not necessarily be treated as

cell, not how the cell is formatted.

Result
3

Result
4

Value Result
35 $ 100.00

35 $ 100.00

Category Result
student $ 700.00
*e* $ 7,770.00
>m $ 700.00
 Criteria Result
>50 $ 4,060.00
<><50 $ 7,770.00

Result
1300

3330

3330

or example, the condition "=*e*" would

p with the condition ">20". If the conditions
s a possibility of conditions overlapping,

hat might overlap

UE+FALSE=1 and TRUE+TRUE=2. This

e conditions is > 0.

Result
3330

7170

Result
50
me interesting things, depending on
range are stored as date values or text

Date Values
3/1/2017 (date value)
1-Mar-17 (date value)
3/1/17 (text value)
Mar 1, 2017 (text value)
Mar 1st 2017 (text value - unrecognized date format)

Result: 4
4
4
1

Date Values
3/1/2017 (date value)
4/1/2017 (date value)
5/1/2017 (date value)
Apr 1, 2017 (text value)
May 1, 2017 (text value)

Result: 3
 Result: 3

or FIND
k to using array formulas or
nsitive matches.

-sensitive) Value Result

student 0
Student 700

Value Result
stu 0
Stu 700

s1<x<4
which means x > 1 AND x < 4), it is also simple to use
ults of the condition x < 4.

Result
(range,"<=1") 2
(range,"<1") 3
F(range,"<=1") 3
F(range,"<1") 4

Result
2
mple above. The following formula would sum values

ge,date_range,"<1/1/2017")

s x < 2 OR x > 3
arily handle OR conditions, such as X < 2 OR X > 3. The

Result
5
5
5
5 <2 >3

s to summarize expenses by category and between two

Category matches "Fuel", (2) the Date is greater than or
ulas used here

ntained in a table of start and end dates. We also want to

resented as just a single day within the Start Dates
Start Dates End Dates
30-May-2017
6-Jun-2017 8-Jun-2017
13-Jun-2017
20-Jun-2017 10-Jul-2017
Check Register Income and Expense Report
Date Payee Category Amount Balance Period Start 1/1/2018
1/1/18 Carry-Over 3,000.00 3,000.00 Period End 1/31/2018
1/2/18 Auto Stop Fuel (35.42) 2,964.58 INCOME Categories
1/5/18 Smith's Groceries (15.32) 2,949.26 Wages 2,354.00
1/15/18 Albertsons Groceries (67.98) 2,881.28 EXPENSE Categories
1/31/18 Paycheck Wages 2,354.00 5,235.28 Fuel (35.42)
2/5/18 A Express Debt (156.23) 5,079.05 Groceries (83.30)
2/9/18 ABC Insure Insurance (576.23) 4,502.82 Insurance -
2/15/18 Auto Stop Fuel (24.12) 4,478.70 Debt -
2/18/18 Albertsons Groceries (76.12) 4,402.58
2/28/18 Paycheck Wages 2,354.00 6,756.58
ense Report
2/1/2018
2/28/2018

2,354.00

(24.12)
(76.12)
(576.23)
(156.23)