Tag: Excel Sumif

Sometimes we may meet the cases that some blank cells exist in criteria range or sum range. In most situations we will ignore them, so we need to filter data by ‘not blank/not empty’. Today we will introduce you how can we sum only for not blank cells if some are blank in criteria range.

Through demonstrate a simple instance, we will introduce you the syntax, arguments of SUMIFS function, and let you know how the formula works step by step and finally reach your goal. After reading the article, you may have a simple understanding of SUMIFS function. We will also introduce you another method to resolve this issue by SUMIF function at the end.

EXAMPLE

Refer to above table, we can see that some different colors of T-shirt are listed in “Product” column. For these T-shirts, due to different colors, some of them are available at a discount. Amount for each color T-shirt is listed in “Amount” column accordingly.

In this instance, we want to calculate total amount for the products which have a discount on sale. So, we need to filter data in ‘Discount’ column based on the criteria that ‘discount field is not blank’. Then we can sum up filtered values in “Amount” column. To resolve this issue by formula, we can apply SUMIFS or SUMIF functions.

FORMULA – SUMIFS FUNCTION

Step 1: Select B2:B7, then in Name Box define a new name for this range, for example ‘Discount’.

Step 2: Select C2:C7, in Name Box define a new name for this range, for example ‘Amount’.

Step 3: In E2, enter the formula =SUMIFS(Amount,Discount,”<>”).

NOTE: In step#1 and step#2 we defined range name “Discount” and “Amount”, when entering the formula, after typing “Amou…”, defined range “Amount” is auto loaded, you can directly select it from dropdown list. You can also select B2:B7, C2:C7 to fill formula arguments as well.

Step 4: Press Enter after typing the formula. A number is returned.

We can see in column B, except B3 and B7, other cells are filled with ‘Yes’ and not empty, the corresponding amounts are 100 (in cell C2), 120 (in cell C4), 150 (in cell C5) and 130 (in cell C6), so the total amount is 100+120+150+130=500. The formula works correctly.

SUMIFS FUNCTION INTRODUCTION

SUMIFS function can be seen as SUM+IFS, it can handle multiple ‘criteria range’ and ‘criteria’ combinations.

For SUMIFS function, the syntax is:

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …). Contents in [] are optional.

SUMIFS function supports wildcards like asterisk ‘*’ and question mark ‘?’, it also supports logical operators within its arguments. If wildcards or logical operators are required, they should be enclosed into double quotes (““) with text.

The usage of wildcards:

1. An asterisk (*) means one or more characters.
2. A question mark (?) means one character.
3. The position of asterisk or question mark means the character(s) position relative to the entered part. For example, “*A*” means characters or texts are both listed before and after “A”. “A*” means this text is started with A, but ends with others.

The usage of logical operators:

1. “>” – greater than
2. “<” – less than
3. “<>” – not equal to

SUMIFS ARGUMENTS EXPLANATION

SUMIFS – SUM RANGE

In our instance, C2:C7 is the ‘sum range’ obviously. We define this range with name ‘Amount’ in above step#2.

In the formula bar, select ‘Amount’, press F9, values in this range are listed in an array.

SUMIFS – CRITERIA RANGE 1

We have only one criteria range in this case, it is B2:B7. This range records if product has a discount.

In the formula bar, select ‘Discount’, press F9, values in this range are listed in an array.

SUMIFS – CRITERIA 1

We want to calculate total amount for the products which has a discount. As we mentioned above, SUMIFS function allows logical operators, so just enter “<>” as criteria. “<>” means not equal to, when it is used as criteria, it means “not empty”.

HOW SUMIFS FORMULA WORKS

After explaining each argument in the formula, now we will show you how the formula works with these arguments.

Refer to above mentioned arguments, the formula is converted into below format in the formula bar.

=SUMIFS({100;200;120;150;130;230},{“Yes”;0;”Yes”;”Yes”;”Yes”;0},”<>”)

In the formula, there is one pair of criteria range and criteria:

Criteria Range: {“Yes”;0;”Yes”;”Yes”;”Yes”;0}

Criteria: “<>”

If cell is not empty, mark it in bold:

{“Yes“;0;”Yes“;”Yes“;”Yes“;0}

So, for bold texts, record a ‘True’ in the array; otherwise, record a ‘False’. Then we can get a new array:

{True;False;True;True;True;False}

Convert ‘True’ to ‘1’ and ‘False’ to ‘0’:

{1;0;1;1;1;0}

Now, we have below two arrays:

Sum Range: {100;200;120;150;130;230}

Criteria: {1;0;1;1;1;0} – if product has a discount, 1 is displayed.

We list the two arrays in two rows, multiply the two numbers in the same column, and save their products in another row, sum all products together, we can get 500 at last.

COMMENTS

1.In this case, as in ‘Discount’ column, only ‘Yes’ is recorded, you can also update the formula to “=SUMIFS(Amount,Discount,”Yes”)”.

2.The formula also works well if the discount is a real number.

3.In this case, we can also use SUMIF function if cells are not blank.

For SUMIF function, the syntax is:

=SUMIF (range, criteria, [sum_range]). Argument in [] is optional. If sum range is omitted, SUMIF will sum up all numbers in range argument. The order of the arguments is different from SUMIFS.

Enter the formula =SUMIF(Discount,”<>”,Amount).

After entering the formula, we can see that SUMIF function also works correctly. Actually, in most situations, SUMIF function can be used instead of SUMIFS function if there is only one pair of criteria range and criteria. If multiple criteria are supplied, you can choose SUMIFS to handle them.

SUMMARY

1. SUMIFS function can handle multiple groups of criteria ranges and criteria. Sum range is the first argument among all arguments.

2. It supports user defined range name.

3. It supports wildcard.

4. It supports logical operators. ”<>” can represent “not equal to/not empty” in a formula.

Related Functions

• Excel SUMIFS Function
  The Excel SUMIFS function sum the numbers in the range of cells that meet a single or multiple criteria that you specify. The syntax of the SUMIFS function is as below:=SUMIFS (sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)…
• Excel SUMIF Function
  The Excel SUMIF function sum the numbers in the range of cells that meet a single criteria that you specify. The syntax of the SUMIF function is as below:=SUMIF (range, criteria, [sum_range])…

We have introduced the method of sum numbers based on criteria “sum if cell begins with” (you can refer to ‘How to Sum Data if Begins with in Excel’ on our website). In this article, we will show you the formula about ‘sum if cell ends with’ with the help of Excel SUMIFS or SUMIF function.

Through demonstrate a simple instance, we will introduce you the syntax, arguments and usage of SUMIFS and SUMIF function, and let you know how this formula works to reach your goal. After reading the article, you may have a simple understanding of SUMIFS/SUMIF function.

EXAMPLE

Refer to above left-hand table, we can see that there are two columns, one is “Date”, another column is “Amount”. “Date” column lists some dates in year 2020 and 2021. “Amount” column lists the amount for “Date” accordingly.

In this instance, we want to calculate sum of year 2021. So, we need to find out all dates belong to year 2021, and then sum up values in “Amount” column. To find out dates belong to year 2021, through above table we can know that they are ended with 2021, so the filter criteria for “Date” range is “ends with 2021”, after all, we can apply SUMIFS or SUMIF here to fix this issue properly. At first, let’s introduce the usage of SUMIFS formula.

FORMULA 1 – APPLY SUMIFS FUNCTION

Step 1: Select A2:A7, then in Name Box define a new name for this range, for example ‘Date’.

Step 2: Select B2:B7, in Name Box define a new name for this range, for example ‘Amount’.

Note: Above two steps are optional, you can directly use cell or range reference like A2:A7, B2:B7 in your formula, but for understanding well, we name ranges in advance. When applying formula, user can directly enter “Amount”, “Date” into the formula.

Step 3: In E2, enter the formula =SUMIFS(Amount,Date,”*2021″).

Step 4: Press Enter after typing the formula.

We can see in “Date” column, dates in cell A3, A5 and A7 are ended with Y2021, the corresponding amounts are 200 (in cell B3), 150 (in cell B5), and 230 (in cell B7), so the total is 200+150+230=580. The formula works correctly.

SUMIFS FUNCTION INTRODUCTION

SUMIFS function can be seen as SUM+IFS, it can handle multiple ‘criteria range’ and ‘criteria’ combinations.

For SUMIFS function, the syntax is:

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …). Contents in [] are optional.

SUMIFS function supports wildcards like asterisk ‘*’ and question mark ‘?’, it also supports logical operators within its arguments. If wildcards or logical operators are required, they should be enclosed into double quotes (““) with text.

The usage of wildcards:

1. An asterisk (*) means one or more characters.
2. A question mark (?) means one character.
3. The position of asterisk or question mark means the character(s) position relative to the entered part.

The usage of logical operators:

1. “>” – greater than
2. “<” – less than
3. “<>” – not equal to

SUMIFS ARGUMENTS EXPLANATION

SUMIFS – SUM RANGE

In our instance, B2:B7 is the ‘sum range’ obviously. Amount values are listed in this field. We define this range with name ‘Amount’ in above step#2.

In the formula bar, select ‘Amount’, press F9, values in this range are listed in an array.

SUMIFS – CRITERIA RANGE 1

A2:A7 is the criteria range. In this instance we have only one criteria range. This range contains different dates in year 2020 and year 2021.

In the formula bar, select ‘Date’, press F9. All dates are displayed in proper order in an array:

SUMIFS – CRITERIA 1

We want to calculate “sum of year 2021”, so we need to find out all dates that ends with year 2021. SUMIFS function supports texts and wildcards, they should be enclosed into double quotes “ “ when applying them, so for criteria 1, we enter “*2021”, “*” means there are some characters before “2021”.

SUMIFS WORKFLOW

After explaining each argument in the formula, now we will show you how the formula works with these arguments.

Refer to above mentioned arguments, we can get below formula:

=SUMIFS({100;200;120;150;130;230},{"Day1 of Y2020";"Day1 of Y2021";"Day2 of Y2020";"Day2 of Y2021";"Day3 of Y2020";"Day3 of Y2021"},"*2021")

There is one pair of criteria range and criteria:

Criteria Range 1: {“Day1 of Y2020″;”Day1 of Y2021″;”Day2 of Y2020″;”Day2 of Y2021″;”Day3 of Y2020″;”Day3 of Y2021”}

Criteria 1: “*2021”

In criteria range 1, compare each value in the array with criterion “*2021”; if date is ended with 2021, mark them in bold:

{“Day1 of Y2020″;”Day1 of Y2021“;”Day2 of Y2020″;”Day2 of Y2021“;”Day3 of Y2020″;”Day3 of Y2021“}

So, for dates which matches the criteria, record a ‘True’ in the array; otherwise, record a ‘False’. Then we can get a new array:

{False;True;False;True;False;True}

Convert ‘True’ to ‘1’ and ‘False’ to ‘0’:

{0;1;0;1;0;1}

Now, we have below two arrays:

Sum range: {100;200;120;150;130;230}

Criteria: {0;1;0;1;0;1} – from above steps we know that if date ends with “2021”, 1 is displayed.

We list the two arrays in two rows, and multiply the two numbers in the same column, and save their product in “Product” row, then sum all values in “Product”.

Refer to above table, we finally get 580 as returned value.

COMMENTS

In this case, asterisk (*) represents one or more characters, if “*” is omit in criteria, that means cell which is exactly equal to “2021” can satisfy the condition. So, if we enter =SUMIFS(Amount,Date,”2021″) to sum numbers, it doesn’t work.

FORMULA 2 – APPLY SUMIF FUNCTION

Now, let’s try to apply SUMIF function to resolve this issue. The first two steps are the same.

Step 3: In E3, enter the formula =SUMIF(Date,”*2021″,Amount).

Note: We can see in SUMIF formula, we use the same parameters compare with SUMIFS, the difference is range and criteria are moved to the first two positions.

Step 4: Press Enter after typing the formula. Verify that we get the same result.

SUMIF FUNCTION INTRODUCTION

SUMIF function can be seen as SUM+IF, it sums numbers based on criteria. it allows user provides one pair of ‘criteria range’ and ‘criteria’.

For SUMIF function, the syntax is:

SUMIF(range, criteria, [sum_range]) – if sum range=range, then sum range can be omitted.

SUMIF function allows wildcards like asterisk ‘*’ and question mark ‘?’, it also allows logical operators within its argument. If wildcards or logical operators are required, they should be enclosed into double quotes (““).

SUMIF ARGUMENTS EXPLAINATION

SUMIFS and SUMIF share the same parameters in this instance.

SUMIF – RANGE

SUMIF – CRITERIA

SUMIF – SUM RANGE

SUMIF WORKFLOW

SUMIFS and SUMIF have the same criteria range, criteria and sum range, they are processed with the same steps.

SUMMARY

1. SUMIFS function can handle multiple pairs of criteria ranges and criteria. Sum range is the first argument among all arguments.

2. SUMIF function can handle one pair of criteria range and criteria. Sum range is the last argument among all arguments.

3. They allow user defined range name, wildcards, logical operators.

Related Functions

• Excel SUMIFS Function
  The Excel SUMIFS function sum the numbers in the range of cells that meet a single or multiple criteria that you specify. The syntax of the SUMIFS function is as below:=SUMIFS (sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)…
• Excel SUMIF Function
  The Excel SUMIF function sum the numbers in the range of cells that meet a single criteria that you specify. The syntax of the SUMIF function is as below:=SUMIF (range, criteria, [sum_range])…

Sometimes we may meet the case that to sum numbers with the pre-condition “less than X”. We only want to sum numbers which are less than a supplied number. In this article, we will show you the method to resolve this problem by formulas with the help of Excel SUMIF and SUMIFS functions.

Through a simple instance below, we will introduce you the syntax, argument and the usage of SUMIF and SUMIFS functions. We will let you know how the formula works step by step to reach your goal clearly. After reading the article, you may have a simple understanding of SUMIF/SUMIFS functions.

EXAMPLE

Refer to the left-hand table, we can see that there are two columns “Score” and “Count”. For each score, its adjacent cell records the number “how many students get this score”.  In the right-hand table, it is a simple summary table. There is only one condition that “score: less than 60”, and our expectation is counting the total number of students whose score is less than 60. In this case, user can enter any value into E2, it is a dynamic value. In the formula, we just use cell reference E2 to represent the value, so, if E2 value is changed, the returned value is also changed accordingly.

As we want to sum numbers from “Count” column based on the criteria “Score < 60 (value in E2)”. We need to find out all scores that meet our requirement, and then sum up values in “Count” column accordingly. To resolve this issue by formula, we can apply Excel SUMIF or SUMIFS functions.

FORMULA 1 – APPLY SUMIF FUNCTION

Step 1: Select A2:A9, then in Name Box define a name for this range, just use the column header ‘Score’.

Step 2: Select B2:B9, in Name Box define a name for this range, for example ‘Count’.

Note: Above two steps are not required, they are optional, you can directly use cell or range reference like A2:A9, B2:B9 in your formula, but for understanding well, we name ranges in advance.

Step 3: In E3, enter the formula =SUMIF(Score,”<“&E2,Count).

Note: In step#1 and step#2 we already name ranges “Score” and “Count”, so when entering the range information into formula, just after typing “Sc…”, our named range “Score” is loaded, just select it from dropdown list. As we explained in step#2, you can also hold and drag A2:A9 to fill the argument as well.

Step 4: Press Enter after typing the formula.

In column A, cell A5 (50), A7 (55) are less than 60, the corresponding counts are 5 (in cell B5), 3 (in cell B7), so the total count is 5+3=8. The formula works correctly.

SUMIF FUNCTION INTRODUCTION

SUMIF function can be seen as SUM+IF, it sums numbers based on criteria. it allows user provides one pair of ‘criteria range’ and ‘criteria’.

For SUMIF function, the syntax is:

SUMIF(range, criteria, [sum_range]) – if sum range=range, then sum range can be omitted.

SUMIF function allows wildcards like asterisk ‘*’ and question mark ‘?’, it also allows logical operators within its argument. If wildcards or logical operators are required, they should be enclosed into double quotes (““).

SUMIF ARGUMENTS EXPLAINATION

SUMIF – RANGE

We need to compare each score in “Score” column with 60, so “A2:A9” is the ‘range’.

In the formula bar, select “Score”, press F9, all values in “Score” are listed in an array.

SUMIF – CRITERIA

We enter “<”&E2 as “criteria”. If logical operator is used, it should be quote within double quotes “”; In E2, we enter “60” to filter scores; to concentrate logical operator “<” and number “60”, we need to use a special character “&” to connect them, if “&” is missing, formula quits typing directly.

In the formula bar, select the completed criteria, press F9.

SUMIF – SUM RANGE

In this instance, we need to count numbers of student whose score is less than 60. So, cell reference “B2:B9” in “Count” column is the ‘sum range’.

In the formula bar, select “Count”, press F9, all values in “Count” are listed in an array.

SUMIF WORKFLOW

After explaining each argument in the formula, now we will show you how the formula works with the help of these arguments.

=SUMIF({60;65;70;50;75;55;80;85},”<60″,{2;5;6;5;2;3;4;2})

We have a pair of range and criteria:

RANGE: {60;65;70;50;75;55;80;85}

CRITERIA: “<60”

Compare each number in the array with 60; if number satisfies the condition “<60”, mark it in bold:

{60;65;70;50;75;55;80;85} – In bold if it is <60

As bold number meets our requirement, so record a ‘True’ for them in the array; otherwise, record a ‘False’ for others.

{Flase;False;False;True;False;True;False;False}

Convert ‘True’ to ‘1’ and ‘False’ to ‘0’ to make this array can be taken into calculation in next step.

{0;0;0;1;0;1;0;0}

Now, we have below two arrays:

{2;5;6;5;2;3;4;2} – Sum Range

{0;0;0;1;0;1;0;0} – from above steps we know that if number is less than 70, 1 is displayed in this number’s position

We list the two arrays in two rows, in the same column multiply the two numbers, and save their product in another row.

Sum up all numbers in the third row. You can use SUM function here and select all values in the third row as reference.

5+3=8

NOTE

If there is no “Count” column, and only “Score” column exists, and you want to sum numbers with the condition “number is less than 60”, you can also use SUMIF function here. In this situation, the sum range and criteria range are the same as there is only one range reference A2:A9, so, the last argument ‘sum range’ can be omitted in the formula.

FORMULA 2 – APPLY SUMIFS FUNCTION

Now, let’s try to apply SUMIFS function to resolve this issue. The first two steps are the same.

Step 3: In E3, enter the formula =SUMIFS(Count,Score,”<“&E2).

Note: We can see in SUMIFS formula, we use the same parameters compare with SUMIF, the difference is “Count” is moved to the first position.

Step 4: Press Enter after typing the formula.

SUMIFS FUNCTION INTRODUCTION

SUMIFS function can be seen as SUM+IFS, it allows user provides multiple ‘criteria range’ and ‘criteria’ combinations.

For SUMIFS function, the syntax is:

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …).

SUMIFS function allows wildcards like asterisk ‘*’ and question mark ‘?’, it also allows logical operators within its argument. If wildcards or logical operators are required, they should be enclosed into double quotes (““).

SUMIFS ARGUMENTS EXPLAINATION

SUMIFS and SUMIF share the same parameters in this instance.

SUMIFS – SUM RANGE

SUMIFS – CRITERIA RANGE

SUMIFS – CRITERIA

SUMIFS WORKFLOW

SUMIFS and SUMIF have the same criteria range, criteria and sum range, they are processed with the same steps exactly.

NOTE

Unlike SUMIF function, if sum range and criteria range are the same in a formula, sum range is still required.

Warning message pops up if missing argument.

SUMMARY

1. SUMIFS function can handle multiple pairs of criteria ranges and criteria. Sum range is the first argument among all arguments.

2. SUMIF function can handle one pair of criteria range and criteria. Sum range is the last argument among all arguments.

3. They allow user defined range name, wildcards, logical operators.

Related Functions

• Excel SUMIFS Function
  The Excel SUMIFS function sum the numbers in the range of cells that meet a single or multiple criteria that you specify. The syntax of the SUMIFS function is as below:=SUMIFS (sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)…
• Excel SUMIF Function
  The Excel SUMIF function sum the numbers in the range of cells that meet a single criteria that you specify. The syntax of the SUMIF function is as below:=SUMIF (range, criteria, [sum_range])…

In previous article, we have introduced you the method to sum if cell is equal to a certain value, you can check “How to Sum Numbers by Formula if Cells Equal to A Certain Value” on our website for reference. By the way, if special characters exist in the target, can we use the same method to sum numbers? In this article, we will show you how to ‘sum if cell contains special character’ by formula with the help of Excel SUMIFS function.

Through demonstrate a simple instance, we will introduce you the syntax, arguments of SUMIFS function, and let you know how the formula works step by step and finally reach your goal. After reading the article, you may have a simple understanding of SUMIFS function. We will also introduce you another method to resolve this issue by SUMIF function at the end.

EXAMPLE

Refer to above table, we can see that some different types of products are listed in “Product” column. Product serial number consists of three parts, and the three parts are connected with special character “-”. Amount for each type product is listed in “Amount” column accordingly.

In this instance, we want to calculate total amount for the product which serial number is combined with “C1-V1-xxxx”. So, only the products which the first two parts are “C1-V1” can meet the condition. We need to find out all proper products, and then sum up values in “Amount” column. To resolve this issue by formula, we can apply SUMIFS or SUMIF functions.

FORMULA – SUMIFS FUNCTION

Step 1: Select A2:A9, then in Name Box define a new name for this range, for example ‘Product’.

Step 2: Select B2:B9, in Name Box define a new name for this range, for example ‘Amount’.

Step 3: In E2, enter the formula =SUMIFS(Amount,Product,”C1-V1-*”).

NOTE: In step#1 and step#2 we defined range name “Product” and “Amount”, when entering the formula, after typing “Amou…”, defined range “Amount” is auto loaded, you can directly select it from dropdown list. You can also select B2:B9, A2:A9 to fill formula arguments as well.

Step 4: Press Enter after typing the formula.

We can see in column A, cell A2, A4 and A8 contain “C1-V1”, the corresponding amounts are 100 (in cell B2), 200 (in cell B4), and 150 (in cell B8), so the total is 100+200+150=450. The formula works correctly.

SUMIFS FUNCTION INTRODUCTION

SUMIFS function can be seen as SUM+IFS, it can handle multiple ‘criteria range’ and ‘criteria’ combinations.

For SUMIFS function, the syntax is:

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …). Contents in [] are optional.

SUMIFS function supports wildcards like asterisk ‘*’ and question mark ‘?’, it also supports logical operators within its arguments. If wildcards or logical operators are required, they should be enclosed into double quotes (““) with text.

The usage of wildcards:

1. An asterisk (*) means one or more characters.
2. A question mark (?) means one character.
3. The position of asterisk or question mark means the character(s) position relative to the entered part. For example, “*A*” means characters or texts are both listed before and after “A”. “A*” means this text is started with A, but ends with others.

The usage of logical operators:

1. “>” – greater than
2. “<” – less than
3. “<>” – not equal to

ALL ARGUMENTS

SUMIFS – SUM RANGE

In our instance, B2:B9 is the ‘sum range’ obviously. Amount values are listed in this field. We define this range with name ‘Amount’ in above step#2.

In the formula bar, select ‘Amount’, press F9, values in this range are listed in an array.

SUMIFS – CRITERIA RANGE 1

A2:A9 is the criteria range. In this instance we have only one criteria range. This range contains different types of products.

In the formula bar, select ‘Product’, press F9, values in this range are listed in an array.

SUMIFS – CRITERIA 1

We want to calculate total amount for the products contain “C1-V1-”. In this criterion a special character “-” exists, as we mentioned above, SUMIFS function supports texts and special character, so just enter “C1-V1-” to fill the criteria. As cells are not ended with “C1-V1-”, the completed combination is “C1-V1-XXXX”, so a “*” is added after “C1-V1-” to represent “XXXX” part. After all, for criteria 1, we enter “C1-V1-*” to fill this argument.

HOW FORMULA WORKS

After explaining each argument in the formula, now we will show you how the formula works with these arguments.

Refer to above mentioned arguments, the formula is converted into below format in the formula bar.

=SUMIFS({100;120;200;150;130;230;150;250},{“C1-V1-0001″;”C1-V2-0001″;”C1-V1-0002″;”B1-P1-0001″;”B1-P1-0002″;”B1-P1-0003″;”C1-V1-0003″;”C1-V2-0002″},”C1-V1-*”)See screenshot below:

See the pair of criteria range and criteria:

Criteria Range 1: {“C1-V1-0001″;”C1-V2-0001″;”C1-V1-0002″;”B1-P1-0001″;”B1-P1-0002″;”B1-P1-0003″;”C1-V1-0003″;”C1-V2-0002”}

Criteria 1: “C1-V1-*”

In criteria range 1, compare each product serial number in the array with “C1-V1-*”; if “C1-V1-” exists in product serial number, mark them in bold:

{“C1-V1-0001“;”C1-V2-0001″;”C1-V1-0002“;”B1-P1-0001″;”B1-P1-0002″;”B1-P1-0003″;”C1-V1-0003“;”C1-V2-0002”}

So, for bold texts, record a ‘True’ in the array; otherwise, record a ‘False’. Then we can get a new array:

Array 1: {True;False;True;False;False;False;True;False}

Convert ‘True’ to ‘1’ and ‘False’ to ‘0’:

Array 1: {1;0;1;0;0;0;1;0}

Now, we have below two arrays:

Sum Range: {100;120;200;150;130;230;150;250}

Array 1: {1;0;1;0;0;0;1;0} – from above steps we know that if cell contains “C1-V1-”, 1 is displayed.

We list the two arrays in two rows, in the same column multiply the two numbers, and save their product in another row.

Sum up all numbers in the third row.

100+200+150=450

COMMENTS

In this case, we can also use SUMIF function to sum cell properly.

For SUMIF function, the syntax is:

=SUMIF (range, criteria, [sum_range]). Argument in [] is optional. If sum range is omitted, SUMIF will sum up all numbers in range argument.

In E2 enter the formula =SUMIF(Product,”C1-V1-*”,Amount).

After entering the formula, we can see that SUMIF function also works correctly. Actually, in most situations, SUMIF function can be used instead of SUMIFS function, the difference is SUMIF function can only support one pair of criteria range and criteria. So, if only one criterion is supplied to filter data, you can select either SUMIF or SUMIFS you like to sum cells.

SUMMARY

1. SUMIFS function can handle multiple groups of criteria ranges and criteria. Sum range is the first argument among all arguments.

2. It supports user defined range name.

3. It supports wildcard.

4. It supports logical operators.

Related Functions

• Excel SUMIF Function
  The Excel SUMIF function sum the numbers in the range of cells that meet a single criteria that you specify. The syntax of the SUMIF function is as below:=SUMIF (range, criteria, [sum_range])…
• Excel SUMIFS Function
  The Excel SUMIFS function sum the numbers in the range of cells that meet a single or multiple criteria that you specify. The syntax of the SUMIFS function is as below:=SUMIFS (sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)…

In previous article, we introduce the method to sum numbers for cells are equal to a certain. In this article, we will show you the resolution of ‘sum if cells are not equal to a certain value’ by formula with the help of Excel SUMIF function. Through demonstrate a simple instance, we will introduce you the syntax, arguments of SUMIF function, and let you know how this formula works to reach our goal. Some other functions like SUMIFS can also resolve this issue, we will also introduce SUMIFS function a little bit in the end to let you know another way of sum number with condition. After reading the article, you may have a simple understanding of SUMIF function.

EXAMPLE

Refer to above table, we can see that we sale only one kind of fruit each week. We list week number in column A, some kinds of fruits in column B, fruits are on sale alternatively. Total amount for each week for specific fruit is listed in column C.

In this instance, we want to calculate total amount for fruits except fruit ‘Apple’. That means we need to sum up numbers in column C which B column in the same row is not equal to ‘Apple’. We can apply a formula contains SUMIF function to resolve this issue.

FORMULA – SUMIF FUNCTION

Step 1: Select B2:B9, then in Name Box define a new name for this range, for example ‘Fruit’.

Step 2: Select C2:C9, in Name Box define a new name for this range, for example ‘Amount’.

Step 3: In F2, enter the formula =SUMIF(Fruit,”<>Apple”,Amount).

Step 4: Press Enter after typing the formula.

We can see in column A there are five cells are not equal to text ‘Apple’, the corresponding amounts are 120 (in cell C3), 200 (in cell C4), 130 (in cell C6), 230 (in cell C7) and 250 (in cell C9), so the total is 120+200+130+230+250=930. The formula works correctly.

SUMIF FUNCTION INTRODUCTION

SUMIF function can be seen as SUM+IF, it can handle one combination of ‘criteria range’ and ‘criteria’.

For SUMIF function, the syntax is:

=SUMIF (range, criteria, [sum_range]). Argument in [] is optional. If sum range is omitted, SUMIF will sum up all numbers in range argument.

SUMIF function supports wildcards like asterisk ‘*’ and question mark ‘?’, it also supports logical operators within its arguments. If wildcards or logical operators are required, they should be enclosed into double quotes (““) with text.

The usage of wildcards:

1. An asterisk (*) means one or more characters.
2. A question mark (?) means one character.
3. The position of asterisk or question mark means the character(s) position relative to the entered part.

The usage of logical operators:

1. “>” – greater than
2. “<” – less than
3. “<>” – not equal to

ALL ARGUMENTS

SUMIF – RANGE

In our instance, B2:B9 is the ‘range’ obviously. We define this range with name ‘Fruit’ in precondition.

In the formula bar, select ‘Fruit’, press F9, values in this range are listed in an array.

SUMIF – CRITERIA

We can supply a criterion to check if values in criteria range meet this criterion. As we want to calculate total amount for fruits except ‘Apple’, equivalent to calculate total amount for both Banana and Orange. If we sum numbers based on fruit which is equal to ‘Banana’ or ‘Orange’, ‘Banana’ and ‘Orange’ cannot be both included in formula as we can only supply one criterion. So, in criteria position we enter ‘<>Apple’,’<>’ means ‘is not equal to’. We need to look up all cells which are not equal to ‘Apple’ from criteria range. SUMIF function supports text, but they should also be enclosed into double quotes “ “.

SUMIFS – SUM RANGE

C2:C9 is the sum range. We define this range with name ‘Amount’ in precondition, it is easy to understand than just entering ‘C2:C9’ in sum range position in formula. In the formula bar, amounts are displayed in proper order in an array:

HOW FORMULA WORKS

Below analysis can help us know the process of this formula reaching the goal step by step clearly.

After introducing each argument, we get two arrays and a text within double quotes, the formula is converted into below format in the formula bar.

=SUMIF({"Apple";"Banana";"Orange";"Apple";"Banana";"Orange";"Apple";"Banana"},"<>Apple",{100;120;200;150;130;230;150;250})

See screenshot below:

As mentioned above, range and criteria are:

Criteria range: {“Apple”;”Banana”;”Orange”;”Apple”;”Banana”;”Orange”;”Apple”;”Banana”}

Criteria: “<>Apple”

Compare each text in ‘range’ array with supplied criteria ‘is not equal to Apple’. If “Apple” occurs, it doesn’t meet the supplied criteria, so mark a ‘False’ in this position; if text is not ‘Apple’, then mark a ‘True’ in this position.

So, after comparing we get below array:

{False;True;True;False;True;True;False;True}

Convert ‘True’ to ‘1’ and ‘False’ to ‘0’:

{0;1;1;0;1;1;0;1}

Now, we have below two arrays:

Array 1: {100;120;200;150;130;230;150;250} – sum range

Array 2: {0;1;1;0;1;1;0;1} – if cell <> ‘Apple’, 1 is displayed.

We list the two arrays in two rows, and create a new array ‘Array3’ to save numbers based on different values in array2. For example, in array1, value will be copied to array3 only if array2 value in the same column is “1”, otherwise, “0” will be recorded in array3 instead of the original value. So, we get below table finally:

In array2, 1 means in this position the original value is not equal to ‘Apple’; Array1 records amount for each kind of fruit in array2. Above all, numbers which are not zero in array3 are the amounts of ‘Banana’ and ‘Orange’ after filtering. Now, sum up all values in array3.

120+200+130+230+250=930

COMMENTS

1. To understand the formula easily, we define range name in precondition. If you ignore this step, you can directly enter the range reference in formula.

2. For this instance, you can also apply SUMIFS function.

Enter =SUMIFS(Amount,Fruit,”<>Apple”) instead of applying SUMIF formula.

We can get the same result based on above formula. You can see that the order of arguments is a little different than SUMIF function.

SUMIFS function can be seen as SUM+IFS, so it can handle multiple criteria range and criteria combinations. As we mentioned above, we want to sum numbers for ‘Banana’ and ‘Orange’, so, can we supply two criteria ‘Banana’ and ‘Orange’ within SUMIFS function in this case? Like =SUMIFS(Amount,Fruit,”Banana”,Fruit,”Orange”) ? In fact, we cannot set ‘Banana’ and ‘Orange’ as two criteria, because in criteria range there is no one fruit can meet both two criteria ‘Banana’ and ‘Orange’. Fruit is either banana or orange, it cannot be both. So, if we enter =SUMIFS(Amount,Fruit,”Banana”,Fruit,”Orange”) to sum numbers, it doesn’t work.

SUMMARY

1. SUMIF function can handle one group of criteria ranges and criteria. Sum range is optional. Sum range is the last argument among all arguments.

2. SUMIFS function can handle multiple groups of criteria ranges and criteria. Sum range is the first argument among all arguments.

3. They both support user defined range name.

4. They both support wildcard.

5. They both support logical operators.

Related Functions

• Excel SUMIF Function
  The Excel SUMIF function sum the numbers in the range of cells that meet a single criteria that you specify. The syntax of the SUMIF function is as below:=SUMIF (range, criteria, [sum_range])…
• Excel SUMIFS Function
  The Excel SUMIFS function sum the numbers in the range of cells that meet a single or multiple criteria that you specify. The syntax of the SUMIFS function is as below:=SUMIFS (sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)…

We may meet the case that sum numbers based on a certain value in daily work. To sum numbers based on a certain value, we can define this value as a criterion. Then sum up all numbers which meet the condition. The scenario can be simple or complex in real life. But no matter how complex it is, we can use proper excel function to resolve problem.

In this article, we will show you the resolution of ‘sum if cells equal to a certain value’ by formula with the help of Excel SUMIFS function. Through demonstrate a simple instance, we will introduce you the syntax, arguments of SUMIFS function, and let you know how this formula works for reaching the target. Actually, some other functions like SUMIF (or the combination of excel functions) can also resolve this issue, we will also mention SUMIF function a little bit in the end to let you know another way of sum number with condition. After reading the article, you may have a simple understanding of SUMIFS function, and also a supplement of you excel skills

EXAMPLE

Refer to above table, we can see that we sale only one kind of fruit each week. We list week number in column A, some kinds of fruits in column B, fruits are on sale alternatively. Total amount for each week for specific fruit is listed in column C.

In this instance, we want to calculate total amount for apple. That means we need to sum up data in column C which B column in the same row equals to ‘Apple’ exactly. We can apply a formula contains SUMIFS function to resolve this issue.

FORMULA – SUMIFS FUNCTION

Step 1: Select B2:B9, then in Name Box define a new name for this range, for example ‘Fruit’.

Step 2: Select C2:C9, in Name Box define a new name for this range, for example ‘Amount’.

Step 3: In F2, enter the formula =SUMIFS(Amount,Fruit,”Apple”).

Step 4: Press Enter after typing the formula.

We can see in column A there are three cells are equal to text ‘Apple’, the corresponding amounts are 100 (in cell C2), 150 (in cell C5) and 150 (in cell C8), so the total is 100+150+150=400. The formula works correctly.

SUMIFS FUNCTION INTRODUCTION

SUMIFS function can be seen as SUM+IFS, it can handle multiple ‘criteria range’ and ‘criteria’ combinations.

For SUMIFS function, the syntax is:

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …). Contents in [] are optional.

ALL ARGUMENTS

SUMIFS – SUM RANGE

In our instance, C2:C9 is the sum range obviously. We define this range with name ‘Amount’ in precondition, it is easy to understand than just entering ‘C2:C9’ in sum range position in formula.

In the formula bar, select ‘Amount’, press F9, values in this range are listed in an array.

SUMIFS – CRITERIA RANGE

B2:B9 is the criteria range. In this instance we have only one criteria range. This range contains three kinds of fruits.

In the formula bar, these fruits are displayed in proper order in an array:

SUMIFS – CRITERIA

We want to calculate total amount for ‘Apple’ only. We need to look up all cells equal to ‘Apple’ from criteria range. So, in criteria position we enter ‘Apple’. SUMIFS function supports texts, but they should be enclosed into double quotes “ “ when applying them.

HOW FORMULA WORKS

After introducing each argument, we get two arrays and a text within double quotes, the formula is converted into below format in the formula bar.

=SUMIFS({100;120;200;150;130;230;150;250},{"Apple";"Banana";"Orange";"Apple";"Banana";"Orange";"Apple";"Banana"},"Apple")

See screenshot below:

As mentioned above, the criteria range, criteria are:

Criteria range: {“Apple”;”Banana”;”Orange”;”Apple”;”Banana”;”Orange”;”Apple”;”Banana”}

Criteria: “Apple”

In criteria range, “Apple” occurs three times, at the first position, the fourth position and the seventh position; So, these positions are marked with ‘True’ after comparing with text ‘Apple’, the others are not ‘Apple’, so they are marked with ‘False’.

So, after comparing we get below array:

{True;False;False;True;False;Flase;True;False}

Convert ‘True’ to ‘1’ and ‘False’ to ‘0’:

{0;0;1;0;1;0;0;0}

Now, we have below two arrays:

Array 1: {100;120;200;150;130;230;150;250} – sum range

Array 2: {1;0;0;1;0;0;1;0} – if cell equals to ‘Apple’, 1 is displayed.

We list the two arrays in two rows, and create a new array ‘Array3’ to save numbers based on different values in array2. For example, in array1, value will be copied to array3 only if array2 value in the same column is “1”, otherwise, “0” will be recorded in array3 instead of the original value. So, we get below table finally:

In array2, 1 means in this position the original value is ‘Apple’; Array1 records amount for each kind of fruit in array2. Above all, numbers which are not zero in array3 are the amounts of ‘Apple’ after filtering. Now, sum up all values in array3.

100+0+0+150+0+0+150+0=400

Above analysis can help us know the process of this formula reaching the goal step by step clearly.

COMMENTS

1. To understand the formula easily, we define range name in precondition. If you ignore this step, you can directly enter the range reference in formula.

2. For this instance, you can also apply SUMIF function.

Enter =SUMIF(B2:B9,”Apple”,C2:C9) instead of applying SUMIFS formula.

We can get the same result. You can see that the order of arguments is a little different than SUMIFS function.

SUMMARY

1. SUMIFS function can handle multiple groups of criteria ranges and criteria. Sum range is first argument among all arguments.

2. SUMIFS function supports user defined range name.

Related Functions

• Excel SUMIF Function
  The Excel SUMIF function sum the numbers in the range of cells that meet a single criteria that you specify. The syntax of the SUMIF function is as below:=SUMIF (range, criteria, [sum_range])…
• Excel SUMIFS Function
  The Excel SUMIFS function sum the numbers in the range of cells that meet a single or multiple criteria that you specify. The syntax of the SUMIFS function is as below:=SUMIFS (sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)…

For a set of data, sometimes we may need to sum some of them based on criteria ‘if begins with/end with/contains’ ** characters. In our daily work, this case frequently occurs when calculating total for one or more items among a lot of different type of items.

To resolve this problem, we can apply excel SUMIF and SUMIFS functions. For these two functions, ‘criteria’ is an important argument, so through providing proper criteria range, criteria and sum range, we can filter out data matches the entered criteria and then sum data based on the filtered data.

In this article, we will show you the formula which can ‘sum data if begins/end with or contains ** characters’ based on SUMIF or SUMIFS functions. These two functions are frequently used in sum data with ‘Criteria’. We will introduce above two functions with simple examples, descriptions, screenshots and explanations in this article, and also show you the usage of them. As the two functions look similar, we will also introduce the difference between them. At last, we will let you know the function/formula workflow step by step clearly. After reading the following article, I’m sure you can have a simple understanding of SUMIF or SUMIFS functions.

EXAMPLE:

In above table, the first item is ‘Fresh Fruit’, so we can sum data based on criteria in A column which begins with ‘Fresh’; for the second item ‘Candy’, we can see ‘Orange Candy’, ’Grape Candy’ are end with ‘Candy’, so we can sum data based on criteria ends with ‘Candy’; for the third item ‘Apple’, ‘Fresh Apple’, ’Apple Juice’ and ‘Apple Pie’ contain ‘Apple’, so we can sum data based on criteria contains ‘Apple’. We will apply formula contains SUMIF or SUMIFS to sum data refer to above three scenarios.

FORMULA – SUMIF FUNCTION

Step 1: In E2, enter the formula =SUMIF($A$2:$A$9,”Fresh*”,$B$2:$B$9). ‘Fresh’ is the criteria, we add an asterisk (*) after ‘Fresh’, it means one or more characters exist following word ‘Fresh’.

Step 2: Press Enter after typing the formula.

‘Fresh Apple:100’+’Fresh Orange:150’+’Fresh Grape:150’=400. We get 400 by applying the formula. So the formula works properly.

If we omit (*) in criteria ‘Fresh*’, 0 is returned as there is no criteria exactly equals to ‘Fresh’.

Step 3: In E3, enter the formula =SUMIF($A$2:$A$9,”*Candy”,$B$2:$B$9). ‘Candy’ is the criteria, we add an asterisk (*) before ‘Candy’, it means one or more characters exist before word ‘Fresh’.

Step 4: Press Enter after typing the formula. ‘Orange Candy:200’+’Grape Candy:250’=450.

Step 5: In E4, enter the formula =SUMIF($A$2:$A$9,”*Apple*”,$B$2:$B$9). ‘Apple’ is the criteria, we add asterisk (*) before and after ‘Apple’, then all contents contain ‘Apple’ are filtered, amounts for filtered ‘Apple’ are summed.

HOW FORMULA WORKS:

For SUMIF function, the syntax as below:

SUMIF(range, criteria, [sum range])

The first argument ‘range’ is the criteria range, in our instance, for three items, the criteria range is the same range ‘$A$2:$A$9’, we add $ before range A2:A9 to lock this range, so when copy this formula to other cells, reference A2:A9 is will not be auto changed.

The second argument ‘criteria’ is the criteria we sum data based on. In above applied formulas, the criteria are “Fresh*”, “*Candy” and “*Apple*” respectively. It reflects that SUMIF function can support wildcards in its formula, and it requires to add double quotes (““) to enclose wildcards or texts. An asterisk (*) means one or more characters. A question mark (?) means one character. The position of asterisk or question mark means the character(s) position relative to the entered part.

The third argument ‘sum range’ lists values can be used for sum. Through the first two arguments, we can filter data in criteria range based on the criteria, after filtering, amount for the filtered data are summed.

This formula can be seen like this:

=SUMIF({“Fresh Apple”;”Apple Juice”;”Apple Pie”;”Fresh Orange”;”Orange Juice”;”Orange Candy”;”Fresh Grape”;”Grape Candy”},”Fresh*”,{100;120;200;150;130;200;150;250})

If texts in criteria range begins with ‘Fresh’, then the result is TRUE, otherwise the result is FALSE. So after comparing, the result can be seen as {TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE,FALSE}. So the formula is equivalent to {1,0,0,1,0,0,1,0}*{100;120;200;150;130;200;150;250}={100,0,0,150,0,0,150,0}. SUM data in this array, we can get 400. In E2 the result is 400 as well.

Through above analysis process, we can also analysis the working process for the formulas in E3 and E4. They are exactly the same because we only apply SUMIF function here.

FORMULA – SUMIFS FUNCTION

Step 1: In E2 enter the formula =SUMIFS($B$2:$B$9,$A$2:$A$9,”Fresh*”).

Step 2: In E3 enter the formula =SUMIFS($B$2:$B$9,$A$2:$A$9,”*Candy”).

Step 3: In E4 enter the formula =SUMIFS($B$2:$B$9,$A$2:$A$9,”*Apple*”).

After pressing Enter, we can see we get the same results by applying SUMIF function.

HOW FORMULA WORKS:

SUMIFS function looks similar to SUMIF, it only has an extra ‘S’ than SUMIF. SUMIF function can be seen as SUM+IF, it has only one group of ‘criteria range’ and ‘criteria’, but SUMIFS function can be seen SUM+ IFS, it can have multiple ‘criteria range’ and ‘criteria’ combinations.

For SUMIFS function, it has below arguments:

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …).

In our instance, $B$2:$B$9 is the sum range, it provides values to do sum calculation. If we want to set it as an absolute range, we can add $ before column and row to lock the range. Then when copying formula to other cells, this range is fixed.

$B$2:$B$9 =({100;120;200;150;130;200;150;250}

$A$2:$A$9 is the criteria range, in this case we have only one criteria range.

{“Fresh Apple”;”Apple Juice”;”Apple Pie”;”Fresh Orange”;”Orange Juice”;”Orange Candy”;”Fresh Grape”;”Grape Candy”}

“Fresh*”, “*Candy” and “*Apple*” are criteria for the three formulas respectively. We compare each value in criteria range $A$2:$A$9 with the entered text, if the entered text can be found with correct position, corresponding amount value in sum range $B$2:$B$9 will be summed. By this way, we get total amount based on the criteria. Above all, the formula in E4 can be seen as below:

=SUMIFS({100;120;200;150;130;200;150;250},{“Fresh Apple”;”Apple Juice”;”Apple Pie”;”Fresh Orange”;”Orange Juice”;”Orange Candy”;”Fresh Grape”;”Grape Candy”},”*Apple*”)

For the following steps, you can refer to SUMIF function working process.

SUMMARY:

1. SUMIF function has only one group of criteria range and criteria, sum range is listed in the last position in the formula.
2. SUMIFS function has multiple groups of criteria ranges and criteria, sum range is listed in the first position of the formula.
3. They are frequently used in summing data based on criteria.

Related Functions

• Excel SUMIF Function
  The Excel SUMIF function sum the numbers in the range of cells that meet a single criteria that you specify. The syntax of the SUMIF function is as below:=SUMIF (range, criteria, [sum_range])…
• Excel SUMIFS Function
  The Excel SUMIFS function sum the numbers in the range of cells that meet a single or multiple criteria that you specify. The syntax of the SUMIFS function is as below:=SUMIFS (sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)…