How to Sum in Vertical Range

If we want to add numbers based on some conditions in Excel worksheet, we can add criteria with the help of SUMIFS function, SUMIFS can filter data with multiple criteria effectively. In this article, we will introduce you the method about applying SUMIFS function to sum numbers with different vertical ranges.

We will introduce you the syntax, arguments of SUMIFS 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 function and know how can we use this function to sum numbers with vertical range.

1. Sum in Vertical Range Example

How to Sum in Vertical Range 1

Refer to the upper side table, it lists the amount for ‘Apple Pie’, ‘Hamburger’ and ‘Salad’ on weekdays (from ‘Monday’ to ‘Friday’) in two weeks. Start from column C, it lists the amount for the three kinds of food on each weekday for two weeks in vertical direction. In the bottom side table, it records the total amount for the three kinds of food in two weeks.

Now, we need to create a formula that can return the total amount for each food in two weeks based on different weekdays. This question can be seen as we need to sum data from a vertical range with specific criteria. For example, in this instance, in C9, we want to enter a formula that can return the total amount for ‘Apple Pie’ on ‘Monday’ in ‘two weeks’ (week1 and week2). Besides, when dragging the formula to other cells (in range reference C9 to G11), we need the formula can adjust cell reference and range reference automatically and return the correct result based on updated parameters properly.

In this instance, we need to pick up numbers with criteria from each vertical range, then sum them together. To fix this issue by formula, we can apply excel built-in function SUMIFS here.

2. Sum in Vertical Range Formula – SUMIFS FUNCTIONS

Step1: In C9, enter the formula

=SUMIFS(C$2:C$7,$B$2:$B$7,$B9)
How to Sum in Vertical Range 2

Step2: Press Enter after typing the formula.

How to Sum in Vertical Range 3

We can see in cell C9 result 9 is returned, it is equal to 5 from C2 plus 4 from C4, the formula works correctly.

Step3: Drag the ‘Auto Fill’ handle to make range reference C9:G11 filled with the formula. Then we can see that results are automatically calculated and displayed in each cell correctly.

How to Sum in Vertical Range 4

3. SUMIFS FUNCTION INTRODUCTION

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

Syntax:

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

[] part can be omitted.

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

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

The formula is =SUMIFS(C$2:C$7,$B$2:$B$7,$B9). Refer to above SUMIFS function introduction, we will split it to several parts.

SUMIFS – SUM RANGE

C$2:C$7 is the ‘sum range’ in this case.

You can see we add “$” before row index 2 and 7, that’s because when dragging the formula from C9 to C10 (formula is moved in vertical direction), cell reference will be moved in vertical direction by default, so we need add “$” before row index to lock the range in vertical direction, make sure the sum range can only be adjusted in vertical, but in horizontal, it is absolutely fixed.

See screenshot below, if “$” is omitted, formula is updated to =SUMIFS(C3:C8,$B$2:$B$7,$B10) in C10, sum range is moved from range reference C2:C7 to C3:C8 improperly, the returned result is 9 incorrectly.

How to Sum in Vertical Range 5

Select C$2:C$7 from formula in the formula bar, the press F9, values in this range are expanded in an array.

How to Sum in Vertical Range 6

SUMIFS – CRITERIA RANGE

$B$2:$B$7 is the criteria range. We have only one criteria range in this instance.

Criteria range contains three kinds of food, due to there are two weeks, so we list them twice.

You can see we add “$” before column and row index both, that’s because range $B$2:$B$7 is an absolute range. No matter copy formula to which cell reference, the criteria range is still $B$2:$B$7 and will not be changed. So we add ‘$’ to lock this range.

Select $B$2:$B$7 from formula in the formula bar, the press F9, values in this range are expanded in an array.

How to Sum in Vertical Range 7

SUMIFS – CRITERIA

$B9 is the criteria. $B9=”Apple Pie”.

As we want to calculate total amount for the provided food, so can set criteria as B9 (B9=”Apple Pie”), B10 (B10=”Hamburger”), and B11 (B11=”Salad”). As column B is fixed, so we add ‘$’ before column B.

We can also enter food name directly, as SUMIFS function supports texts, but they should be enclosed into double quotes “”, so for criteria “Apple Pie”, we can directly enter “Apple Pie” in criteria argument.

Select $B9 from formula in the formula bar, the press F9, criteria value is displayed.

How to Sum in Vertical Range 8

4. Sum in Vertical Range – Formula Explantation

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

After expanding values in each range reference or cell reference, in the formula bar, the formula is displayed as:

=SUMIFS({5;10;5;4;12;4},{"Apple Pie";"Hamburger";"Salad";"Apple Pie";"Hamburger";"Salad"},"Apple Pie")

There is one pair of criteria range and criteria.

{“Apple Pie”;”Hamburger”;”Salad”;”Apple Pie”;”Hamburger”;”Salad”} – Criteria Range

{“Apple Pie”} – Criteria

If values from the criteria range are matched with the criteria “Apple Pie”, “True” will be recorded and saved, otherwise, “False” will be saved instead. So, after comparing, we can get a new array:

{True;False;False;True;False;False} – after comparing with criteria “Apple Pie”

For the following logical operation, “True” is coerced to ‘1’ and ‘False’ is coerced to ‘0’. So above array is converted to below array which consists of numbers “1” and “0”.

{1;0;0;1;0;0} – for criteria “Apple Pie”

Now, we have below two pairs of arrays:

{5;10;5;4;12;4} – sum range

{1;0;0;1;0;0} – for criteria “Apple Pie”

Multiply the two elements in the same position in the two arrays. Then we can get a new array.

{5;0;0;4;0;0}

Add all products in above array, we get 9 finally. Actually, in step#2, SUMIFS function returns 9 after calculation. For the other cells, the workflow is similar.

COMMENTS

1. In this case, as we have only one pair of criteria range and criteria, we can also apply SUMIF function as well. The difference is SUMIF function only supports one group of criteria range and criteria, and its sum range is listed at the end.

Enter =SUMIF($B$2:$B$7,$B9,C$2:C$7), we can get the same result as applying SUMIFS.

How to Sum in Vertical Range 9

5. Video: How to Sum in Vertical Range in Excel

In this video, you will learn how to use the SUMIF formula to sum values in a vertical range in Excel.

6. 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], …)…
  • Excel SUM function
    The Excel SUM function will adds all numbers in a range of cells and returns the sum of these values. You can add individual values, cell references or ranges in excel.The syntax of the SUM function is as below:= SUM(number1,[number2],…)

How to Sum Values Based on Month and Year in Excel

We often do some summary or statistic at the end of one month or one year. In these summary tables, there are at least two columns, one column records the date, and the other column records the sales or product number or total costs etc. If we want to calculate the total values based on a certain month of a year, for example if we want to calculate the total sales for January 2020, how can we do? This article will help you to solve this problem.

See the example below. A column lists some dates. B column lists the sales. E column lists the month and year we want to calculate the total sales for. F column is used for showing the returned value by formula.

Sum Values Based on Month 1

1. Sum Values Based on Month and Year by SUMIF Function

Step 1: In cell F2, enter the formula:

 =SUMIFS(B2:B16,A2:A16,">="&DATE(2019,1,1),A2:A16,"<="&DATE(2019,1,31))

In SUMIFS function, B2:B16 is the sum range, A2:A16 is the criteria range. “>=”&DATE(2019,1,1) and “<=”&DATE(2019,1,31) are the two criteria. Details please see below screenshot.

Sum Values Based on Month 2

Verify that total sales 1500 is displayed for January 2019 after calculation.

Sum Values Based on Month 3

Step 2: If we want to calculate total sales based on a certain period, we can change the parameters in DATE function. For example, to calculate the total sales for period 1/3/2019 – 1/3/2020, we can enter the formula:

 =SUMIFS(B2:B16,A2:A16,">="&DATE(2019,1,3),A2:A16,"<="&DATE(2020,1,3))
Sum Values Based on Month 4

Above all, you can change the date in DATE function to do sum per your demands.

2. Sum Values Based on Month and Year by SUMPRODUCT

Step 1: In cell F2, enter the formula

=SUMPRODUCT((MONTH(A2:A16)=1)*(YEAR(A2:A16)=2019)*(B2:B16))

In this formula we also use MONTH function and YEAR function to filter date from range A2:A16 based on criteria ‘Date is included in period January 2019’. B2:B16 is the sum range.

Sum Values Based on Month 5

Step 2: Click Enter to get the result. Verify that we get the same result in method #1 step#1.

Sum Values Based on Month 6

3. Sum Values Based on Month and Year with User Defined Function (VBA Code)

If you want to sum values based on Month and Year with User Defined Function in Excel, you can use these steps:

Step1: Open the Visual Basic Editor by pressing Alt + F11.

Step2: In the Visual Basic Editor, click on “Insert” from the menu bar and select “Module” to create a new module.

Step3: In the new module, enter the following code, save the module with a suitable name.

Function SumValuesByMonthAndYear(sumRange As Range, dateRange As Range, sumMonth As Integer, sumYear As Integer) As Double
    Dim i As Integer
    Dim sum As Double
    
    For i = 1 To sumRange.Rows.Count
        If Month(dateRange.Cells(i, 1).Value) = sumMonth And Year(dateRange.Cells(i, 1).Value) = sumYear Then
            sum = sum + sumRange.Cells(i, 1).Value
        End If
    Next i
    
    SumValuesByMonthAndYear = sum
    
End Function

Step4: go back to your Excel worksheet and enter the following formula without the quotes into a blank cell.

=SumValuesByMonthAndYear(B2:B16, A2:A16, 1, 2019)

This function takes four arguments:

  • sumRange – The range of cells containing the values to sum
  • dateRange – The range of cells containing the dates to check for the month and year
  • sumMonth – The month to sum for (as an integer from 1 to 12)
  • sumYear – The year to sum for (as a four-digit integer)

The function then loops through the dateRange, checks if each date matches the sumMonth and sumYear, and adds the corresponding value from the sumRange to the sum variable.

Finally, the function returns the sum variable as the result.

How to Sum Values Based on Month and Year vba 2.png

4. Video: Sum Values Based on Month and Year

This video will demonstrate how to Sum Values Based on Month and Year in Excel using the Formula and VBA Code.

5. Related Functions

  • Excel SUMPRODUCT function
    The Excel SUMPRODUCT function multiplies corresponding components in the given one or more arrays or ranges, and returns the sum of those products.The syntax of the SUMPRODUCT function is as below:= SUMPRODUCT (array1,[array2],…)…
  • Excel MONTH Function
    The Excel MONTH function returns the month of a date represented by a serial number. And the month is an integer number from 1 to 12.The syntax of the MONTH function is as below:=MONTH (serial_number)…
  • Excel DATE function
    The Excel DATE function returns the serial number for a date.The syntax of the DATE function is as below:= DATE (year, month, day)…
  • Excel YEAR function
    The Excel YEAR function returns a four-digit year from a given date value, the year is returned as an integer ranging from 1900 to 9999. The syntax of the YEAR function is as below:=YEAR (serial_number)…

How to Sum in Horizontal Range

To add numbers together we need to apply SUM function. But if we want to add numbers based on some conditions, we can add criteria with the help of SUMIFS function, SUMIFS can filter data with multiple criteria effectively. In this article, we will introduce you the method of applying SUMIFS function about sum numbers from different horizontal ranges.

We will introduce you the syntax, arguments of SUMIFS 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 function and know how can we use this function to sum numbers with horizontal range.

1. Sum in Horizontal Range using Formula

a. Example Introduction

How to Sum in Horizontal Range1

Refer to the left-hand side table, it lists the amount for ‘Apple Pie’, ‘Hamburger’ and ‘Salad’ on weekdays (from ‘Monday’ to ‘Friday’) in two weeks. Start from row 3, in horizontal, it lists the amount for the three kinds of food on each weekday in two weeks. In the right-hand side table, it records the total amount for the three kinds of food in two weeks.

Now, we need to create a formula that can return the total amount for each food in two weeks based on different weekdays. This question can be seen as we need to sum data from a horizontal range with specific criteria.

For example, in this instance, in I3, we want to enter a formula that can return the total amount for ‘Apple Pie’ on ‘Monday’ in ‘two weeks’ (week1 and week2). Besides, when dragging the formula to other cells (in range reference I3 to K7), we need the formula can adjust cell reference and range reference automatically and return the correct result based on updated parameters properly.

In this instance, we need to pick up numbers with criteria from each horizontal range, then sum them together. To fix this issue by formula, we can apply excel built-in function SUMIFS here.

b. How to Use SUMIFS FORMULA

Step1: In I3, enter the formula =SUMIFS($A3:$G3,$A$2:$G$2,I$2).

How to Sum in Horizontal Range2

Step2: Press Enter after typing the formula.

How to Sum in Horizontal Range3

We can see in cell I3 result 9 is returned, it is equal to 5 from B3 plus 4 from E3, the formula works correctly.

Step3: Drag the ‘Auto Fill’ handle to make range reference I3 to K7 filled with the formula. Then we can see that results are automatically calculated and displayed in each cell correctly.

How to Sum in Horizontal Range4

c. How The SumIFS Formula Works

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

After expanding values in each range reference or cell reference, in the formula bar, the formula is displayed as:

=SUMIFS({5,10,5,4,12,4},{"Apple Pie","Hamburger","Salad","Apple Pie","Hamburger","Salad"},"Apple Pie")

There is one pair of criteria range and criteria.

{"Apple Pie","Hamburger","Salad","Apple Pie","Hamburger","Salad"} 
{"Apple Pie”}

If values from the criteria range are matched with the criteria “Apple Pie”, “True” will be recorded and saved, otherwise, “False” will be saved instead. So, after comparing, we can get a new array:

{True,False,False,True,False,False} 

For the following logical operation, “True” is coerced to ‘1’ and ‘False’ is coerced to ‘0’. So above array is converted to below array which consists of numbers “1” and “0”.

For criteria “Apple Pie”:

{1,0,0,1,0,0} 

Now, we have below two pairs of arrays:

sum range:

{5,10,5,4,12,4} 

For criteria “Apple Pie”:

{1,0,0,1,0,0} 

Elements are vertical-aligned in the two arrays. Multiply the two elements in the same position in the array. Then we can get a new array.

{5,0,0,4,0,0} 

Add all products in above array, we get 9. Actually, in step#2, SUMIFS function returns 9 after calculation. For the other cells, the workflow is similar.

Enter =SUMIF($B$2:$G$2,I$2,$B3:$G3), we can get the same result as applying SUMIFS.

How to Sum in Horizontal Range9

2. 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], …)…
  • Excel SUM function
    The Excel SUM function will adds all numbers in a range of cells and returns the sum of these values. You can add individual values, cell references or ranges in excel.The syntax of the SUM function is as below:= SUM(number1,[number2],…)

If Cell Contain Specific Text

Cell contains specific tex

This article will explain how to check if a cell contains text or a specific text string in Google Sheets or Microsoft Excel spreadsheets. In our daily work, we often look for specific text in worksheets containing large amounts of data or need to determine if a specific cell area contains text values.

We may also encounter the need to find out if a cell contains partial text or we need to find a specific text string in a cell by using VBA code.

Cell Contains Specific Text in Excel & Google Sheets

Below we will show how to check if a cell contains a specific text by using a formula in Excel or Google Sheets.

If you want to check if a given cell contains a specific text, then you can use the SEARCH function in combination with the ISNUMBER function to create a nested formula. You can refer to this general formula below.

=ISNUMBER(SEARCH(specific_text,text))

In the general formula above, specific_text is the specific text you want to find, and text is the text string or cell reference you want to find that contains the specific text.

If you found a specific text, then the formula will return TRUE, otherwise it will return FALSE.

If you need to find partial text from the cell, then the SEARCH function is very suitable; by default, the SEARCH function will automatically find partial text to match.

For the example in this article, you can use the following formula to find specific text.

=ISNUMBER(SEARCH(B4,A4))
Cell contains specific text

From the screenshot above, we can see that the formula supports partial text matching, where specific_text is “rest” and the text is “interest dividends“, and the result returned is TRUE, which means that partial text matching for a specific character is successful.

Let’s see how this formula works:

When the SEARCH function finds a specific text, it returns the position of that text in the searched text cell, and if not found, it returns #VALUE! . So, we can take advantage of this feature by simply using the ISNUMBER function to check if the SEARCH function returns a number, and if it is a number, it means that the specific text was found in the cell.

For the ISNUMBER function, it returns TRUE if the given argument is a number, otherwise it returns FALSE. therefore, when the SEARCH function can return the position of a specific character, then the ISNUMBER function returns TRUE. if the SEARCH function returns a #VALUE error, then the ISNUMBER function will return FALSE.

Cell Contains Specific Text with Case Sensitive in Excel & Google Sheets

You can use the SEARCH function to find specific text and partial text matches, but the function is not case-sensitive.

If you want to check for specific text in a case-sensitive manner in a text cell, then you cannot use the SEARCH function, and you need to replace the SEARCH function with the FIND function, using the following formula.

=ISNUMBER(FIND(B4,A4))
Cell contains specific text

As you can see from the above figure, specific_text “Rest” can no longer be successfully matched in text cell A4.

If Cell Contains Specific Text Then in Excel & Google Sheets

If you want to do something else when you find a cell contains a specific text, for example, we do not want to return TRUE or FALSE results, but rather return some more meaningful text or results.

You can build an IF nested formula by nesting the above formula into the IF function as follows.

=IF(ISNUMBER(SEARCH(B4,A4)),"Found", "No Found")
Cell contains specific text

Note: Because this formula uses the SEARCH formula, the result is case-insensitive.

Sum If Cell Contains Text in Google sheets & Excel

If a column of cells contains a specific text string, you want to add the corresponding cell value of another column, then you can use the SUMIFS function contains a specific text of the cells corresponding to the data of another column to sum. The general formula is as follows.

=SUMIFS(sum_range, criteria_range,specific_text)

If you want to find all the cells in the cell region A4:A12 contains the “expen” string, and the corresponding value of another column B4:B12 to sum, then you can use the following formula.

=SUMIFS(B4:B12,A4:A12, "*expen*")
Cell contains specific text

If you are using google sheets, then you can also accomplish this task by using the following formula, which is as follows.

=SUM(filter(B4:B12, regexmatch(A4:A12,"expen")))
Cell contains specific text

As you can see from the chart above, the formula consisting of the SUM function and the FILTER function returns the same result as the EXCEL formula.

Note: Excel does not have a special wildcard formula that uses the “*” meta-character to match any character. There is a special regular expression function regexmatch in google sheet.

Conditional Formatting If Cell Contains in Google Sheets & Excel

In Microsoft Excel, if you want to highlight a text cell containing the “expense” string, then you can refer to the following steps.

STEP 1: Select the cell range A4:A12, then in the HOME tab, click the Conditional Formatting button, and then click the New Rule submenu.

Cell contains specific text

STEP2: The New Formatting Rule window will pop up, click on “Use a formula to determine which cells to format“, then enter the following formula in the text box.

=SEARCH(“expense”,A4)
Cell contains specific text

STEP3: Click the Format button and select a color to fill the cells that can be matched successfully. Click OK.

Cell contains specific text

STEP 4: You will see that all cells that contain the expense string are automatically highlighted.

Cell contains specific text

In google sheets, if you want to highlight a text cell that contains an “expense” string, then you can refer to the following steps.

STEP1: Select the cell range that contains a specific text to be highlighted by conditional formatting

Cell contains specific text

STEP 2: Click Format menu, then click Conditional Formatting submenu, the “Conditional format rules” window will pop up on the right side of the page.

Cell contains specific text

STEP 3: Select Text Contains in the Format rules drop-down list and enter the specific text string “expense” in the text box

Cell contains specific text

STEP 4: Select a highlight color in Formatting style and click the Done button. 

Cell contains specific text

STEP 5: Let’s see the final result.

Cell contains specific text

Related Functions

  •  Excel ISNUMBER function
    The Excel ISNUMBER function returns TRUE if the value in a cell is a numeric value, otherwise it will return FALSE.The syntax of the ISNUMBER function is as below:= ISNUMBER (value)…
  • Excel SUM function
    The Excel SUM function will adds all numbers in a range of cells and returns the sum of these values. You can add individual values, cell references or ranges in excel.The syntax of the SUM function is as below:= SUM(number1,[number2],…)…
  • Excel SEARCH function
    The Excel SEARCH function returns the number of the starting location of a substring in a text string.The syntax of the SEARCH function is as below:= SEARCH  (find_text, within_text,[start_num])…
  • Excel Find function
    The Excel FIND function returns the position of the first text string (substring) from the first character of the second text string.The FIND function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the FIND function is as below:= FIND  (find_text, within_text,[start_num])…
  • 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 Filter function
    The FILTER function extracts matched records from a collection of data using one or more logical checks. The include argument specifies logical tests, which might encompass a wide variety of formula conditions.==FILTER(array,include,[if empty])…

Average Of Numbers With Multiple Criteria In Excel

Have you ever come across a task to calculate the average of the numbers with respect to multiple criteria? Are you tired of doing this cumbersome task manually? Are you willing to do this task smartly in just a matter of seconds? Then congratulations because you have just landed on the right article.

In this article, you will get to know the easiest way to calculate the average of the numbers with respect to multiple criteria in no time.

So read this article till the end, and let’s dive into it;

5 Average cells based on multiple criteria1

General Formula

=AVERAGEIFS(values,range1,criteria1,range2,criteria2)

Explanations for Syntax:

Before learning how to use the above formula, let’s first understand the role of each syntax in quickly calculating the average of the numbers concerning multiple criteria.

  • AVERAGEIFS: In Excel, this function returns the average of a set of input values that meet multiple criteria. Learn more about the AVERAGEIFS Function.
  • Comma symbol (,): It acts as a separator that helps to separate a list of values.
  • Parenthesis (): The main function of this symbol is to group the elements.
  • Range: It represents the input value from your worksheet.
  • Criteria: The specific criteria or values used to calculate the average.

Explanation

You can use the AVERAGEIFS function to average numbers based on multiple criteria. In the example, the formula in H2  is as follows:

=AVERAGEIFS(sales,product,E2,region,F2)

5 Average cells based on multiple criteria1

Where “sales” (C2:C9), “product” (A2:A9), and “sales” (C2:C9) are named ranges.

The AVERAGEIFS function, like the COUNTIFS and SUMIFS functions, is intended to handle multiple criteria that are entered in [range, criteria] pairs. AVERAGEIFS’s behavior is fully automatic as long as this information is provided correctly.

In the example, we have product values in column E and region values in column F. We use these values directly by using cell references as criteria.

The first argument holds the range of values to average:sales range

To limit the calculation by product, we provide:product, E2

We use the following formula to limit the calculation by region:region, F2

The answer in cell H2 is 396:

Now, let’s go to the 2nd example in which we want to average the product of “excel” and the sales greater than 300. Please follow these instructions:

Enter the formula given below into a blank cell:

=AVERAGEIFS(sales,product,E2,sales,F2)

5 Average cells based on multiple criteria1

(product range is the data that contains the criteria1, sales range is the range which we want to calculate the average, E2 and F2 are the criteria1 and criteria 2), then press the Enter key to get the desired result. Take a look at this screenshot:

Note: If you need more than two criteria, add the criteria ranges and criteria you require as follows:

=AVERAGEIFS(sales,product,E2,region,F2,sales,G2)

5 Average cells based on multiple criteria1

Note: the first criteria range is product and E2, the second criteria range is region and F2 and the third criteria is sales and G2 and the range you want to average the values is the criteria, sales range.

For Better understanding, we are adding up the 3rd example as follows:

  • Consider the example below to calculate the average of numbers based on multiple criteria.
  • The image below will show the input values for Columns B, C, and D.
  • Next, enter the given formula in the formula bar section.
  • Finally, we’ll see the result in cell H3.

5 Average cells based on multiple criteria1

Summary

This article explains how to use the formulas in Excel to calculate the average of numbers with multiple criteria. I hope you found this article interesting. If you have any ideas, please share them with us.

Related Functions

  • Excel AVERAGEIFS function
    The Excel AVERAGEAIFS function returns the average of all numbers in a range of cells that meet multiple criteria.The syntax of the AVERAGEIFS function is as below:= AVERAGEIFS (average_range, criteria_range1, criteria1, [criteria_range2, criteria2],…)….
  • Excel COUNTIFS function
    The Excel COUNTIFS function returns the count of cells in a range that meet one or more criteria. The syntax of the COUNTIFS function is as below:= COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)…
  • 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], …)…

How to Sum if Equal to Many Items or A Range in Excel

If we want to sum numbers from a range with criteria, we often select SUMIF of SUMIFS functions as the first choice to create a formula. The criteria can be a number or an array or a collection of some items. In this article, we will show you to sum numbers based on a range of items. In fact, there are many functions can be applied to help us to figure out this problem, and in real life based on different situations, you can select different functions. But in this article, we will show you the way to resolve this problem by using SUMIF/SUMPRODUCT functions combination. We will introduce you the syntax, arguments and basic usage of these functions, and let you know the working process of our formula.

EXAMPLE

How to Sum if Equal to Many Items 1

Refer to the left-hand side table, we can see some kinds of fruits are listed in “Fruit” column. And amounts are listed in “Amount” column. In the right-hand side table, we list the items we want to sum amounts for them. In previous instance, we have learned how to sum amounts for one item only, for example, we can apply SUMIF of SUMIFS function to create a formula “=SUMIF(A2:A13,D2,B2:B13)” to sum amounts for “Apple”.

How to Sum if Equal to Many Items 2

But in this case, sum criteria contain two items “Apple” and “Orange”, and the total amount is recorded in one cell and it covers the two items. Thus, only applying SUMIF function cannot figure out our problem, even we can create a formula like “=SUMIF(A2:A13,D2,B2:B13)+SUMIF(A2:A13,D3,B2:B13)” to calculate total amount, it is clear but complex, and when there are multiple items, the formula will be very long. So, we need to find a proper way to sum. In fact, there are many ways to sum numbers based on criteria contain a range reference, for this instance, we will show you to sum amounts for many items by SUMPRODUCT and SUMIF functions.

Before designing a formula, we can name range references.

Select range A2:A13, in name box enter “Fruit”, then press Enter.

How to Sum if Equal to Many Items 3

Select range B2:B13, in name box enter “Amount”, then press Enter.

How to Sum if Equal to Many Items 4

Select range D2:D3, in name box enter “Items”, then press Enter.

How to Sum if Equal to Many Items 5

CREATE A FORMULA with SUMPRODUCT & SUMIF FUNCTIONS

1.STEPS

Step 1: In E2, enter the =SUMPRODUCT(SUMIF(Fruit,Items,Amount)).

How to Sum if Equal to Many Items 6

You can enter named range like “Fruit” into your formula directly, or you can also select the range by dragging the handle as well.

Step 2: Press Enter after typing the formula.

How to Sum if Equal to Many Items 7

We can see in column A, cell A2, A3, A6, A8, A9 and A11 meet our criteria, so we just need to sum numbers from B2, B3, B6, B8, B9 and B11, so the total amount is 5000+6150+5500+4000+8500+7500=36650. The formula works correctly.

2.FUNCTION INTRODUCTION

SUMIF function can be seen as SUM+IF. It can sum up numbers by specific condition.

Syntax:

 =SUMIF(criteria_range, criteria, sum_range)

SUMPRODUCT function can return sum of products.

Syntax:

 =SUMPRODUCT(array1, [array2], [array3], ...)

For both SUMPRODUCT and SUMIF functions, they support wildcards like asterisk ‘*’ and question mark ‘?’, also support logical operators like ‘>’,’<’. If wildcards or logical operators are required, they should be enclosed into double quotes (““).

3.ALL ARGUMENTS

In this case, SUMIF is included in SUMPRODUCT formula as its argument.

SUMIFS – RANGE

Range reference “Fruit” is the criteria range.

In the formula bar, select “Fruit”, press F9, values in this range are expanded in an array.

How to Sum if Equal to Many Items 8

SUMIFS – CRITERIA

As we want to sum amount for “Apple” and “Orange” together, so the criteria here is a range reference “Items”.

In the formula bar, select “Items”, press F9, items in this range are expanded in an array.

How to Sum if Equal to Many Items 9

As we said we can use “=SUMIF(A2:A13,D2,B2:B13)+SUMIF(A2:A13,D3,B2:B13)” to sum total for both two items, in fact we can use an array constant like {D2;D3} to shorten the formula.

Note: an array constant can provide a set of values, and the values are hard-coded. It is frequently used in array formula.

SUMIF – SUM RANGE

Range reference “Amount” is the “sum range”.

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

How to Sum if Equal to Many Items 10

4.HOW THE FORMULA WORKS

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

After expanding values in each range reference, in the formula bar, the formula is displayed as:

=SUMPRODUCT(SUMIF({"Apple";"Orange";"Banana";"Peach";"Orange";"Banana";"Apple";"Orange";"Banana";"Apple";"Grape";"Banana"},{"Apple";"Orange"},{5000;6150;4500;6000;5500;6500;4000;8500;9000;7500;8500;8000}))

A pair of criteria range and criteria:

{"Apple";"Orange";"Banana";"Peach";"Orange";"Banana";"Apple";"Orange";"Banana";"Apple";"Grape";"Banana"}

– Criteria Range

{“Apple”;”Orange”} – Criteria (more than one items)

If items from criteria range match either of criteria “Items”, “True” will be recorded in the array, otherwise, “False” will be recorded. So, after comparing with “Apple” and “Orange” separately, the original criteria range is split to two new arrays which only contains “True” and “False”.

{True;False;False;False;False;False;True;False;False;True;False;False} – compare with “Apple”

{False;True;False;False;True;False;False;True;False;False;False;False} – compare with “Orange”

For the following logical operation, “True” is coerced to “1” and “False” is coerced to “0”. So, values in the array are converted to numbers:

{1;0;0;0;0;0;1;0;0;1;0;0} – compare with “Apple”

{0;1;0;0;1;0;0;1;0;0;0;0} – compare with “Orange”

Sum amount of “Apple” refer to sum range:

{1;0;0;0;0;0;1;0;0;1;0;0} – filter “Apple” in criteria range

{5000;6150;4500;6000;5500;6500;4000;8500;9000;7500;8500;8000} – sum_range

Multiply the values from the two arrays in the same position. Then we save the products in an array:

{5000;0;0;0;0;0;4000;0;0;7500;0;0}

Sum amount of “Orange” refer to sum range:

{0;1;0;0;1;0;0;1;0;0;0;0} – filter “Orange” in criteria range

{5000;6150;4500;6000;5500;6500;4000;8500;9000;7500;8500;8000} – sum_range

Multiply the values from the two arrays in the same position. Then we save the products in an array:

{0;6150;0;0;5500;0;0;8500;0;0;0;0}

SUMPRODUCT function will sum all products: (5000+4000+7500)+(6150+5500+8500)=16500+20150=36650

In this case if we directly select SUMIF part and press F9, array {16500;20150} is returned.

How to Sum if Equal to Many Items 11

We can also use SUM function to replace SUMPRODUCT function in this case.

How to Sum if Equal to Many Items 12

SUMMARY

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

2. SUMPRODUCT function can return the sum of products.

3. They all support wildcards, logical operators.

Related Functions


  • Excel SUMPRODUCT function
    The Excel SUMPRODUCT function multiplies corresponding components in the given one or more arrays or ranges, and returns the sum of those products.The syntax of the SUMPRODUCT function is as below:= SUMPRODUCT (array1,[array2],…)…
  • 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])…

 

 

How to Sum if Equal to X or Y in Excel

In daily work, if we want to sum numbers from a range, and only sum the numbers which being equal to X or Y in the range, we can create a formula with Excel build-in functions to get the result. In fact, there are many functions can be applied to help us figure out the problem. In this article, we will show you three different ways to resolve this problem by using SUMIFS/SUMIF/SUMPRODUCT functions. We will introduce you the syntax, arguments and basic usage of these functions, and let you know the working process of different formulas.

EXAMPLE

How to Sum if Equal to X or Y 1

Refer to the left-hand side table, we can see some tasks in software test progress are listed, for each task, there are several related test cases, for example, for task “Install”, there are 3 test cases assigned to it. In “Status” column, there are three status “Pass”, “Fail” and “Block”. For the tasks which status is “Fail” or “Block”, we need to re-test the related test cases, so we need to know how many test cases we need to select and execute in next regression test. Now, we need to calculate the re-run test case number. There are many ways to sum numbers based on specific criteria, for this instance, we will show you how to sum numbers by formula with different functions SUMIF/SUMIFS/SUMPRODUCT. We will enter different formulas into H2, H3, H4 of “Total case” column accordingly in the right-hand side table.

Before designing a formula, we can name range references.

Select range C2:C9, in name box enter ‘Case’, then press Enter.

How to Sum if Equal to X or Y 2

Select range D2:D9, in name box enter ‘Status’, then press Enter.

How to Sum if Equal to X or Y 3

CREATE A FORMULA with SUM & SUMIFS FUNCTIONS

1.STEPS

Step 1: In H2, enter the =SUM(SUMIFS(Case,Status,{“Block”,”Fail”})).

=SUMPRODUCT(C2:C9*(D2:D9={"Block","Fail"}))

How to Sum if Equal to X or Y 4

Step 2: Press Enter after typing the formula.

How to Sum if Equal to X or Y 5

We can see in column D, cell D3, D5 and D8 meet our criteria, so we just need to calculate case numbers from C3, C5 and C8, so the total case number is 2+1+2=5. The formula works correctly.

2.FUNCTION INTRODUCTION

SUM function can add numbers together. It adds all supplied values from a range or a formula.

Syntax:

=SUM (number1, [number2], [number3], ...)

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

Syntax:

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

For both SUM and SUMIFS function, they support wildcards like asterisk ‘*’ and question mark ‘?’, also support logical operators like ‘>’,’<’. If wildcards or logical operators are required, they should be enclosed into double quotes (““).

3.ALL ARGUMENTS

In this case, SUMIFS is included in SUM formula, it is the only argument of SUM function, its returned values should be accumulated by SUM function.

SUMIFS – SUM RANGE

In our instance, range reference ‘Case’ is the ‘sum range’. Test case numbers are listed in this field.

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

How to Sum if Equal to X or Y 6

SUMIFS – CRITERIA RANGE 1

Range reference ‘Status’ is the criteria range. In this instance we have only one criteria range. We have only three status “Pass”, “Fail” and “Block”.

In the formula bar, select “Status”, pree F9, values in this range are expanded in an array.

How to Sum if Equal to X or Y 7

SUMIFS – CRITERIA 1

As we want to calculate total test case number for tasks which “status=fail” or “status=block”, so if one condition is matched, test case number will be recorded and accumulated. So, we will supply two criteria “Fail” and “Block” in this case, they are expanded saved in one array, a comma is added between them to split the two criteria. As SUMIFS function supports texts and wildcards, but they should be enclosed into double quotes “”, so for criteria 1, we enter {“Fail”,”Block”}.

How to Sum if Equal to X or Y 8

Actually, the two criteria can be split to two “criteria1” in two different SUMIFS formulas, for example, if we enter =SUM(SUMIFS(Case,Status,”Fail”),SUMIFS(Case,Status,”Block”)) in H2, we can get the same result.

How to Sum if Equal to X or Y 9

This formula is clear but looks a little bit complex, so we use an array constant with two elements to shorten the formula.

4.HOW THE FORMULA WORKS

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

After expanding values in each range reference, in the formula bar, the formula is displayed as:

=SUM(SUMIFS({3;3;2;1;1;2;2;2},{"Pass";"Pass";"Block";"Pass";"Fail";"Pass";"Pass";"Fail"},{"Block","Fail"}))

If value from the criteria range can match either criteria, “True” will be recorded in this position, otherwise, “False” will be saved instead.

{“Pass”;”Pass”;”Block”;”Pass”;”Fail”;”Pass”;”Pass”;”Fail”} – Criteria Range

{“Block”,”Fail”} – Criteria Collection

So, after comparing, we can get two new arrays based on different criteria:

{False;False;True;False;False;False;False;False} – for criteria “Block”

{False;False;False;False;True;False;False;True} – for criteria “Fail”

For the following logical operation, “True” is coerced to “1” and “False” is coerced to “0”. So above arrays are converted to two arrays only contain numbers “1” and “0”.

{0;0;1;0;0;0;0;0} – for criteria “Block”

{0;0;0;0;1;0;0;1} – for criteria “Fail”

Now, we have below two pairs of arrays:

{3;3;2;1;1;2;2;2} – sum_range

{0;0;1;0;0;0;0;0} – for criteria “Block”

{3;3;2;1;1;2;2;2} – sum_range

{0;0;0;0;1;0;0;1} – for criteria “Fail”

In the two groups, for the elements in the same position, multiply the two elements. Then we can get two new arrays.

{0;0;2;0;0;0;0;0} – for criteria “Block”

{0;0;0;0;1;0;0;2} – for criteria “Fail”

Add all products in above two arrays separately, we get 2 and 3. Actually, if ignore above analysis, directly select SUMIFS part and press F9, a simple array {2,3} is returned.

How to Sum if Equal to X or Y 10

Now, the SUM function will add the two numbers together, then we get 5 as last.

CREATE A FORMULA with SUM & SUMIF FUNCTIONS

SUMIF function is similar with SUMIFS, they have the same arguments, the difference is it can only handle one criteria range with one criterion, and sum range is listed in the end.

Syntax: =SUMIF(criteria_range, criteria, sum_range)

In this case, all arguments are the same for SUMIF and SUMIFS, as SUMIF only supports one group of criteria range and criteria, so we need to create two SUMIF formulas, and use SUM function to sum two returned values.

In H3, enter the formula =SUM(SUMIF(Status,{“Block”,”Fail”},Case)).

How to Sum if Equal to X or Y 11

Press Enter to get result. We can see we get the same result.

How to Sum if Equal to X or Y 12

CREATE A FORMULA with SUMPRODUCT FUNCTION

Different with SUMIFS or SUMIF function, SUMPRODUCT function can directly return proper result and ignore SUM function.

1.STEPS

Step 1: In H4, enter the =SUMPRODUCT(Case*(Status={“Block”,”Fail”})).

How to Sum if Equal to X or Y 13

Step 2: Press Enter after typing the formula.

How to Sum if Equal to X or Y 14

2.FUNCTION INTRODUCTION

SUMPRODUCT function can be seen as SUM+PRODUCT.

Syntax:

=SUMPRODUCT(array1, [array2], [array3], ...)

3.ALL ARGUMENTS

SUMPRODUCT – ARRAY1

In this case, we have only one array, it is a formula “Case*(Status={“Block”,”Fail”})”.

In the formula bar, select “Status”, pree F9, values in this range are expanded in an array.

How to Sum if Equal to X or Y 15

Compare each status from range ‘Status’ with the criteria, as there are eight elements in criteria range, and two elements in criteria, so after comparing, system will return a two-dimension array (2*8) in the formula bar.

Just select ({“Pass”;”Pass”;”Block”;”Pass”;”Fail”;”Pass”;”Pass”;”Fail”}={“Block”,”Fail”}), then press F9. Verify that comparative result is displayed.

How to Sum if Equal to X or Y 16

Select “Case”, then press F9, all values from range “Case” are expanded.

How to Sum if Equal to X or Y 17

4.HOW THE FORMULA WORKS

After expanding values in each range reference, in the formula bar, the formula is displayed as:

=SUMPRODUCT({3;3;2;1;1;2;2;2}*{FALSE,FALSE;FALSE,FALSE;TRUE,FALSE;FALSE,FALSE;FALSE,TRUE;FALSE,FALSE;FALSE,FALSE;FALSE,TRUE})

Notes: For the following logical operation, “True” is coerced to “1” and “False” is coerced to “0”. The formula is updated to =SUMPRODUCT({3;3;2;1;1;2;2;2}*{0,0;0,0;1,0;0,0;0,1;0,0;0,0;0,1}) in calculation progress.

In the formula bar, select {3;3;2;1;1;2;2;2}*{FALSE,FALSE;FALSE,FALSE;TRUE,FALSE;FALSE,FALSE;FALSE,TRUE;FALSE,FALSE;FALSE,FALSE;FALSE,TRUE} and press F9. We can see that one new array consists of products of previous two arrays are listed.

How to Sum if Equal to X or Y 18

Select =SUMPRODUCT({0,0;0,0;2,0;0,0;0,1;0,0;0,0;0,2}), press F9 to do the last step – SUMPRODUCT function will add products in the array together. As 2+1+2=5, so we get 5 by this formula.

How to Sum if Equal to X or Y 19

SUMMARY

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

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

3. SUMPRODUCT function can return the sum of products.

4. They all support wildcards, logical operators.

Related Functions


  • Excel SUMPRODUCT function
    The Excel SUMPRODUCT function multiplies corresponding components in the given one or more arrays or ranges, and returns the sum of those products.The syntax of the SUMPRODUCT function is as below:= SUMPRODUCT (array1,[array2],…)…
  • 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])…

 

How to Sum Last N Days in Excel

In our daily life, we may want to sum amounts or sales for a specific period, for example in last N days. Sum numbers in Excel is easy to run, we can apply SUM function. But if we want to sum numbers based on criteria, we can SUMIF or SUMIFS function, compare with SUMIF function, SUMIFS can handle multiple criteria effectively.

In this article, we will provide a simple case to show you the way we use SUMIFS function to sum amounts for different items in last 3 days. We will introduce you the syntax, arguments of SUMIFS function, create a formula to sum numbers per our demand. We will also split the formula to several parts and let you know how the formula works step by step. After reading the article, you may have a simple understanding about SUMIFS function and you can use it to sum in last n days.

EXAMPLE

How to Sum in Last N Days 1

Refer to the left-hand side table, we can see some kinds of fruits and their amounts on different dates are listed, total three columns. Based on dates, we can see there are four groups of “Apple, Orange and Banana”.

In the right-hand side table, in “Fruit” column, we list the three kinds of fruits in the same column but different cells. And in the adjacent “Total” column, total amount will be calculated and listed in cells properly for each fruit accordingly. We need to enter a formula into F2 to calculate total amount automatically. As we want to just create one formula which also can be applied in cell F3 and F4 directly, so we need the formula can adjust cell reference and range reference automatically and return the correct result based on adjusted references properly.

As we want to sum amounts for different fruit, we have criteria “Fruit=Apple/Orange/Banana” now; Besides, if we want to sum amounts for each fruit in a specific period, for example only sum amounts in last 3 days (today, yesterday and the day before yesterday, current date is 9-May-21 which is on the top in Date column), we will have new criteria “in last 3 days” now. To create a formula to resolve this issue, we can apply SUMIFS function here as it can hand the case with multiple criteria.

FORMULA – SUMIFS FUNCTIONS

Step 1: In F2, enter the formula =SUMIFS($C$2:$C$13,$B$2:$B$13,”>=”&TODAY()-2,$A$2:$A$13,$E2).

How to Sum in Last N Days 2

Step 2: Press Enter after typing the formula.

How to Sum in Last N Days 3

We can see $15,000.00 is returned. The format is correct. Actually, if you didn’t set the format for cell F2 before, only “15000” is returned. Before entering the formula, you need to focus on this cell, and click ‘Dollars’ icon under ‘Number’ section, and also double click on ‘Increase Decimal’ icon to add two decimal places for the returned number.

Now, let’s check the result. in column A, only cell A2, A5, A8 and A11 can meet our criteria “Fruit=Apple”, as we only sum the amounts in last 3 days, so A11 is ignored. We just need to sum amounts from C2, C5 and C8, so the total number is 5000+6000+4000=15000. The formula works correctly.

By the way, if you are confused with cell reference and range refence in the formula, we can also define range name before creating the formula. See steps below:

1. Select A2:A13, then in Name Box enter “Fruit”, press Enter Define reference B2:B13 as “Date”, C2:C13 as “Amount”. See screenshot below.

How to Sum in Last N Days 4

2. Then create the formula =SUMIFS(Amount,Date,”>=”&TODAY()-2,Fruit,$E2) in F2. You can enter “Amount” directly in the formula instead of selecting a range reference.

How to Sum in Last N Days 5

We can get the same result.

How to Sum in Last N Days 6

FUNCTION INTRODUCTION

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

Syntax: =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

For SUMIFS function, is supports wildcards like asterisk ‘*’ and question mark ‘?’, also support logical operators like “>”,”<”. If wildcards or logical operators are required, they should be enclosed into double quotes (““).

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

Today function can return current date. It doesn’t have any argument.

ALL ARGUMENTS

The formula is =SUMIFS($C$2:$C$13,$B$2:$B$13,”>=”&TODAY()-2,$A$2:$A$13,$E2). Refer to above SUMIFS function introduction, we will split it to several parts.

SUMIFS – SUM RANGE

$C$2:$C$13 is the ‘sum range’ in this case.

We add $ before row and column index to make this range as absolute references. When applying the formula to other cells, this range is locked.

Select $C$2:$C$13 from formula in the formula bar, the press F9, values in this range are expanded in an array.

How to Sum in Last N Days 7

SUMIFS – CRITERIA RANGE1

$B$2:$B$13 is the first criteria range. We have two criteria ranges in this instance. The first one provides date criteria.

Select $B$2:$B$13 from formula in the formula bar, the press F9, values in this range are expanded in an array.

How to Sum in Last N Days 8

We found that dates are converted from Date format to General format: {44325;44325;44325;44324;44324;44324;44323;44323;44323;44322;44322;44322}. Each five digits number represents a date.

SUMIFS – CRITERIA1

“>=”&TODAY()-2 is the criteria1.

As we want to sum amounts in last 3 days, except today, we only need to sum amounts on yesterday and the day before yesterday, TODAY()-2 can represent the day before yesterday, and we use “>=” to make sure this day is included in calculation.

Logical operator “>=” is quoted in “”;

Today() returns the number which can represent current date; Actually TODAY function will return current date in Date format, but we can change it to General format by selecting General in Number Format dropdown list in Number section.

How to Sum in Last N Days 9

So, =TODAY()-2 is equal to 44323.

How to Sum in Last N Days 10

Select “>=”&TODAY()-2 from formula in the formula bar, the press F9, “>=”&44323 is displayed.

How to Sum in Last N Days 11

SUMIFS – CRITERIA RANGE2

$A$2:$A$13 is the second criteria range. The second one provides fruit references.

Select $A$2:$A$13 from formula in the formula bar, the press F9, values in this range are expanded in an array.

How to Sum in Last N Days 12

SUMIFS – CRITERIA2

$E2 is the criteria2.

In E2, one kind of fruit “Apple” is recorded. In this case, we can omit $ actually as formula is only applied in F3 and F4 which are in the same column with F2. But if we want to copy this formula in other cells in different horizontal levels, column E will be adjusted in the formula automatically due to position changed, so we can add $ before column to lock it.

Select $E2 from formula in the formula bar, the press F9, “Apple” is displayed.

How to Sum in Last N Days 13

HOW THE FORMULA WORKS

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

After expanding values in each range reference, in the formula bar, the formula is displayed as:

=SUMIFS({5000;6150;4500;6000;5500;6500;4000;8500;9000;7500;8500;8000},{44325;44325;44325;44324;44324;44324;44323;44323;44323;44322;44322;44322},”>=”&44323,{“Apple”;”Orange”;”Banana”;”Apple”;”Orange”;”Banana”;”Apple”;”Orange”;”Banana”;”Apple”;”Orange”;”Banana”},”Apple”)

There are two pairs of criteria range and criteria.

{44325;44325;44325;44324;44324;44324;44323;44323;44323;44322;44322;44322} – Criteria Range1

“>=”&44323 – Criteria1

And

{“Apple”;”Orange”;”Banana”;”Apple”;”Orange”;”Banana”;”Apple”;”Orange”;”Banana”;”Apple”;”Orange”;”Banana”} – Criteria Range2

“Apple” – Criteria2

If values from each criteria range are matched with the criteria, “True” will be recorded and saved, otherwise, “False” will be saved instead. So, after comparing, we can get a new criteria range:

{True;True;True;True;True;True;True;True;True;False;False;False} – after comparing with criteria1

{True;False;False;True;False;False;True;False;False;True;False;False} – after comparing with criteria2

For the following logical operation, “True” is coerced to ‘1’ and ‘False’ is coerced to ‘0’. So above two arrays are converted to below arrays which consist of numbers “1” and “0”.

{1;1;1;1;1;1;1;1;1;0;0;0} – criteria range1

{1;0;0;1;0;0;1;0;0;1;0;0} – criteria range2

As we sum range need to meet the two criteria both, so we just keep the intersection of the two arrays. If in the same position, 1 is recorded for both, keeps 1, otherwise keeps 0.

{1;0;0;1;0;0;1;0;0;0;0;0} – the intersection of the two criteria range

Now, we have below arrays:

{5000;6150;4500;6000;5500;6500;4000;8500;9000;7500;8500;8000} – sum range

{1;0;0;1;0;0;1;0;0;0;0;0} – criteria range

Elements are one-to-one matched in two arrays. Multiply the two elements in the same position in the array. Then we can get a new array.

{5000;0;0;6000;0;0;4000;0;0;0;0;0}

Add all products in above array, we get 15000.

Now, drag down the formula to fill F3 and F4. We can get total amount in last 3 days for each kind fruit properly.

How to Sum in Last N Days 14

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.

5. TODAY function only returns current date. It has no argument.

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], …)…
  • Excel SUM function
    The Excel SUM function will adds all numbers in a range of cells and returns the sum of these values. You can add individual values, cell references or ranges in excel.The syntax of the SUM function is as below:= SUM(number1,[number2],…)
  • Excel TODAY function
    The Excel TODAY function returns the serial number of the current date. So you can get the current system date from the TODAY function. The syntax of the TODAY function is as below:=TODAY()…

How to Sum if Contains an Asterisk

To add numbers together we need to apply SUM function. And if we want to add numbers based on some conditions, we can add criteria with the help of SUMIFS function, SUMIFS can filter data with multiple criteria effectively. If we want to sum cells with special character only, for example sum cells which contains an asterisk “*”, we can use “*~**” to represent the cells. In this article, we will introduce you the syntax, arguments of SUM and SUMIFS function, and why we use “*~**” to stands for the selected items. We will also let you know how the formula works step by step. After reading the article, you may have a simple understanding about SUMIFS function and the usage of asterisk (*).

EXAMPLE

How to Sum if Contains an Asterisk 1

Refer to the left-hand side table, we can see some kinds of drinks and their amounts are listed. Some items are marked with an asterisk (*) in its name for example “Coffee *”, it represents an unknow type of coffee or any coffee is ok. In the right-hand side table, in “Item” column, we can list the kind of drink we want, and in the “Total” column, total amount will be calculated and listed in E2 properly based on the selected items. We need to enter a formula into E2 to calculate total amount automatically. In this case, the items are that marked with an asterisk, now we need to create a formula that can calculate all amounts for those drinks marked with an asterisk. To create a formula, we can apply SUMIFS function here.

FORMULA – SUM & SUMIFS FUNCTIONS

Step 1: In E2, enter the formula

 =SUMIFS(B2:B12,A2:A12,"*~**").

How to Sum if Contains an Asterisk 2

Step 2: Press Enter after typing the formula.

How to Sum if Contains an Asterisk 3

We can see $26.00 is returned. The format is correct. Actually, if you didn’t set the format for cell E2 before, only “26” is returned. Before entering the formula, you need to focus on this cell, and click ‘Dollars’ icon under ‘Number’ section, and also double click on ‘Increase Decimal’ icon to add two decimal places for the returned number.

How to Sum if Contains an Asterisk 4 How to Sum if Contains an Asterisk 5

Now, let’s check the result. in column A, cell A4, A8, A11 and A12 meet our demand, so we just need to sum amounts from B4, B8, B11 and B12, so the total number is 4+4+10+8=26. The formula works correctly.

FUNCTION INTRODUCTION

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

Syntax:

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

[] part can be omitted.

For SUMIFS function, is supports wildcards like asterisk ‘*’ and question mark ‘?’, also support logical operators like “>”,”<”. If wildcards or logical operators are required, they should be enclosed into double quotes (““).

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

Notes:

In this case, as an asterisk stands for one or more characters by itself, so we need to add a “~” before it to make it only stands for special character “*” literally, then Excel can handle it properly and treat it as an asterisk in formula working process. As there might be some other characters before or after special character “*” in items, so we need to add “*” before and after “~*”. Finally, to represent an item contains an asterisk, we use “*~**” to stand for it.

ALL ARGUMENTS

The formula is =SUMIFS(B2:B12,A2:A12,”*~**”). Refer to above SUMIFS function introduction, we will split it to several parts.

SUMIFS – SUM RANGE

B2:B12 is the ‘sum range’ in this case.

Select B2:B12 from formula in the formula bar, the press F9, values in this range are expanded in an array.

How to Sum if Contains an Asterisk 6

SUMIFS – CRITERIA RANGE

A2:A12 is the criteria range. We have only one criteria range in this instance.

Criteria range contains some kinds of drinks, some of them contain an asterisk, we need to pick them up.

Select A2:A12 from formula in the formula bar, the press F9, values in this range are expanded in an array.

How to Sum if Contains an Asterisk 7

SUMIFS – CRITERIA

D2 is the criteria. D2=”*~**”.

As we mentioned before, “~*” can stand for a literal asterisk. “*~**” stands for items that contain one asterisk. If characters are only listed before the asterisk, you can also set “*~*” as criteria.

HOW THE FORMULA WORKS

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

After expanding values in each range reference, in the formula bar, the formula is displayed as:

=SUMIFS({5;6;4;6;5;6;4;8;9;10;8},{"Latte";"Cappuccino";"Coffee *";"Milk Tea";"Green Tea";"Black Tea";"* Tea";"Orange Juice";"Peach Juice";"* Juice";"Apple * Juice"},"*~**")

There is one pair of criteria range and criteria.

{"Latte";"Cappuccino";"Coffee *";"Milk Tea";"Green Tea";"Black Tea";"* Tea";"Orange Juice";"Peach Juice";"* Juice";"Apple * Juice"} – Criteria Range

{“*~**”} – Criteria

If values from the criteria range are matched with the criteria “marked with an asterisk”, “True” will be recorded and saved, otherwise, “False” will be saved instead. So, after comparing, we can get a new array:

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

– after comparing with criteria

For the following logical operation, “True” is coerced to ‘1’ and ‘False’ is coerced to ‘0’. So above array is converted to below array which consists of numbers “1” and “0”.

{0;0;1;0;0;0;1;0;0;1;1} – criteria range “1” means which can meet the criteria

Now, we have below two arrays:

{5;6;4;6;5;6;4;8;9;10;8} – sum range

{0;0;1;0;0;0;1;0;0;1;1} – criteria range

Elements are horizontal-aligned in the two arrays. Multiply the two elements in the same position in the array. Then we can get a new array.

{0;0;4;0;0;0;4;0;0;10;8}

Add all products in above array, we get 26.

COMMENTS

1.In this case, as we have only one pair of criteria range and criteria, we can also apply SUMIF function as well. The difference is SUMIF function only supports one group of criteria range and criteria, and its sum range is listed at the end.

Enter =SUMIF(A2:A12,”*~**”,B2:B12), we can get the same result as applying SUMIFS.

How to Sum if Contains an Asterisk 8

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.

5. To represent an item contains an asterisk, we use “*~**” to stand for it.

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], …)…
  • Excel SUM function
    The Excel SUM function will adds all numbers in a range of cells and returns the sum of these values. You can add individual values, cell references or ranges in excel.The syntax of the SUM function is as below:= SUM(number1,[number2],…)

 

 

 

 

How to Sum with Criteria and Or Logic in Excel

To add numbers together we need to apply SUM function. And if we want to add numbers based on some conditions, we can add criteria with the help of SUMIFS function, SUMIFS can filter data with multiple criteria effectively. If we want to filter data based on multiple criteria with OR logic, we can add array constant to help us. In this article, we will introduce you the syntax, arguments of SUM and SUMIFS function, and let you know how this formula works to reach your goal. After reading the article, you may have a simple understanding the two functions and array constant construction.

EXAMPLE

How to Sum with Criteria and Or Logic in Excel 0

Refer to the left-hand side table, we can see some tasks in software test progress are listed, for each task, there are several related test cases, for example, for task “Install”, there are 3 test cases assigned to it. In “Status” column, there are three status “Pass”, “Fail” and “Block”. For the tasks which status is “Fail” or “Block”, we need to re-test the related test cases, so we need to know how many test cases we need to select and execute in next regression test. Now, we need to calculate the re-run test case number based on the criteria “status=fail or block”, record the result in the right-hand side table “Total case” column accordingly.

In this instance, we need to pick up numbers from fail or block status, then sum them together. To fix this issue by formula, we can apply SUMIFS and SUM functions here.

FORMULA – SUM & SUMIFS FUNCTIONS

Step 1: In G2, enter the formula

=SUM(SUMIFS(C2:C9,D2:D9,{"Block","Fail"})).

How to Sum with Criteria and Or Logic in Excel 1

Step 2: Press Enter after typing the formula.

How to Sum with Criteria and Or Logic in Excel 2

We can see in column D, cell D3, D5 and D8 meet our criteria, so we just need to calculate case numbers from C3, C5 and C8, so the total case number is 2+1+2=5. The formula works correctly.

FUNCTION INTRODUCTION

SUM function can add numbers together. It adds all supplied values from a range or a formula.

Syntax:

=SUM (number1, [number2], [number3], ...)

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

Syntax:

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

[] part can be omitted.

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

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

In this case, SUMIFS is included in SUM formula, it is the only argument of SUM function, its returned values should be accumulated by SUM function.

SUMIFS – SUM RANGE

In our instance, C2:C9 is the ‘sum range’. Test case numbers are listed in this field.

In the formula bar, select ‘C2:C9’, press F9, values in this range are expanded in an array.

How to Sum with Criteria and Or Logic in Excel 3

SUMIFS – CRITERIA RANGE 1

D2:D9 is the criteria range. In this instance we have only one criteria range. We have only three status “Pass”, “Fail” and “Block”.

In the formula bar, select D2:D9, pree F9, values in this range are expanded in an array.

How to Sum with Criteria and Or Logic in Excel 4

SUMIFS – CRITERIA 1

As we want to calculate total test case number for tasks which “status=fail” or “status=block”, so as long as one condition is met, test case number will be recorded and accumulated. So, we will supply two criteria “Fail” and “Block” in this case, they are expanded saved in one array, a comma is added between them to split the two criteria. As SUMIFS function supports texts and wildcards, but they should be enclosed into double quotes “”, so for criteria 1, we enter {“Fail”,”Block”} finally.

How to Sum with Criteria and Or Logic in Excel 5

Actually, the two criteria can be split to two “criteria1” in two different SUMIFS formulas, for example, if we enter =SUM(SUMIFS(C2:C9,D2:D9,”Fail”),SUMIFS(C2:C9,D2:D9,”Block”)) in G2, we can get the same result. This formula is clear but looks complex, so we use an array constant with two elements to shorten the formula.

HOW THE FORMULA WORKS

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

After expanding values in each range reference, in the formula bar, the formula is displayed as:

=SUM(SUMIFS({3;3;2;1;1;2;2;2},{"Pass";"Pass";"Block";"Pass";"Fail";"Pass";"Pass";"Fail"},{"Fail","Block"})

If value from the criteria range can match either value in criteria collection, “True” will be recorded in this position, otherwise, “False” will be saved instead.

{“Pass”;”Pass”;”Block”;”Pass”;”Fail”;”Pass”;”Pass”;”Fail”} – Criteria Range

{“Fail”,”Block”} – Criteria Collection

So, after comparing, we can get two new arrays based on different criteria:

{False;False;False;False;True;False;False;True} – for criteria “Fail”

{False;False;True;False;False;False;False;False} – for criteria “Block”

For the following logical operation, “True” is coerced to ‘1’ and ‘False’ is coerced to ‘0’. So above arrays are converted to two arrays only contain numbers “1” and “0”.

{0;0;0;0;1;0;0;1} – for criteria “Fail”

{0;0;1;0;0;0;0;0} – for criteria “Block”

Now, we have below two pairs of arrays:

{3;3;2;1;1;2;2;2} – sum_range

{0;0;0;0;1;0;0;1} – for criteria “Fail”

{3;3;2;1;1;2;2;2} – sum_range

{0;0;1;0;0;0;0;0} – for criteria “Block”

In the two groups, for the elements in the same position (in vertical alignment), multiply the two elements. Then we can get two new arrays.

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

{0;0;2;0;0;0;0;0}

Add all products in above two arrays separately, we get 3 and 2, actually, SUMIFS function will return {3,2} after calculation.

Now, the SUM function will add the two numbers together, that’s why the formula finally returns correct result 5. You can verify this before expanding values in the formula bar, for example, in the formula bar, select the whole formula SUMIFS(C2:C9,D2:D9,{“Fail”,”Block”}), then press F9, you can get =SUM({3,2}) in the formula bar.

How to Sum with Criteria and Or Logic in Excel 6

COMMENTS

1.In this case, if SUM function is omitted, we will get incorrect result, only 3 is displayed.

How to Sum with Criteria and Or Logic in Excel 7

In fact, in this case SUMIFS function only returns an array contains two elements, it cannot add the two elements together, so we need to add a SUM function to add them.

2. If we want to add more criteria in this case, for example except status, we also add criteria “Software Test=Start Service” or “Software Test=Stop Service”, then we can add new criteria range and new criteria.

Enter

=SUM(SUMIFS(C2:C9,D2:D9,{"Fail","Block"},B2:B9,{"Start Service";"Stop Service"}))

into G2, formula returns 1 as result.

How to Sum with Criteria and Or Logic in Excel 8

In this formula, we can see that in the first array constant, a comma is displayed between the two values, but in the second array constant, a semi-colon is displayed instead of a comma. That’s because the two criteria are expanded in two array constants, each array contains two elements, to filter data based on the two criteria, we need one of them as a row array, and the other one as a column array, then after executing the formula, it will return a two dimensional array.

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], …)…
  • Excel SUM function
    The Excel SUM function will adds all numbers in a range of cells and returns the sum of these values. You can add individual values, cell references or ranges in excel.The syntax of the SUM function is as below:= SUM(number1,[number2],…)

 

 

 

How to Subtotal Values for Groups and Only Keep One Subtotal for A Group in Column

Sometimes we may meet the case that subtotal values for different groups and only record a subtotal value for one group in a column, for other cells in this group, keep them blank. Today we will introduce you how can we subtotal values for groups and record subtotal values only in specific cells.

We can get subtotal through Subtotal function or SUM function in simple cases, but per different requirement, we need to apply different functions or combinations. Today we will provide a formula contains three functions IF/COUNTIF/SUMIFS to solve this issue. Through demonstrate a simple instance, we will introduce you the syntax, arguments of these functions, and let you know how the formula works step by step. After reading the article, you can think about if there are some other ways to solve this problem.

EXAMPLE

How to Subtotal Values for Groups and Only Keep One Subtotal for A Group in Column1 (1)

Refer to above table, we can see that T-shirts are grouped by colors, and amounts for weeks are separately listed. Our expectation is “get subtotal for each product and record them in proper cell”, see example below:

How to Subtotal Values for Groups and Only Keep One Subtotal for A Group in Column1 (2)

We want to 1) save the subtotal values in the cells which background is filled in light orange. 2) For the other cells in this column should keep blank. 3) By the way, we also want to enter only one formula into D2, then though dragging the handle down to fill other cells, subtotal in D5 and D8 should also be calculated properly.

Can we create a formula that can cover above three conditions? Actually, yes.

In this instance, with the help of IF, COUNTIF and SUMIF functions, we can calculate subtotal for each product by only one formula properly.

FORMULA

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

How to Subtotal Values for Groups and Only Keep One Subtotal for A Group in Column3

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

How to Subtotal Values for Groups and Only Keep One Subtotal for A Group in Column4

Step 3: In D2, enter the formula =IF(COUNTIF($B$2:B2,B2)=1,SUMIF($B$2:$B$10,B2,$C$2:$C$10),””).

How to Subtotal Values for Groups and Only Keep One Subtotal for A Group in Column5

NOTE: In step#1 and step#2 we defined “Product” and “Amount” two ranges, when entering the formula, after typing “Amou…”, defined range “Amount” is auto loaded. User defined range can be seen as absolute reference, so when copy formula to other cells, the reference will not be adjusted automatically. If we use range reference B2:B10 in the formula, we need to add “$” to lock the range.

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

How to Subtotal Values for Groups and Only Keep One Subtotal for A Group in Column6

We can see in column D2, subtotal $450 is returned, it is equal to 100+150+200, the total of C2:C4. The formula is correct.

Step 5: Drag the handle down to fill other cells.

How to Subtotal Values for Groups and Only Keep One Subtotal for A Group in Column7

Verify that subtotal values are calculated correctly for each product. Besides, the subtotal is only recorded in the first row of each group, others keep blank.

FUNCTION INTRODUCTION

In this formula we create a formula with IF function which applies COUNTIF and SUMIF functions as its arguments.

IF function will run a logical test first, and based on the result ‘True’ of ‘False’, it chooses to which branch to execute.

For IF function, the syntax is:

=IF (logical_test, [value_if_true], [value_if_false])

How to Subtotal Values for Groups and Only Keep One Subtotal for A Group in Column8

———————————————————————————————-

COUNTIF function can used for counting cell number based on different criteria.

For COUNTIF function, the syntax is:

=COUNTIF (range, criteria)

———————————————————————————————-

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

For SUMIF function, the syntax is:

=SUMIF (range, criteria, [sum_range])

For above three functions, they all allow logical operators like “>”,”>”,”<>” and wildcards like “*”,”?”.

FUNCTION ARGUMENTS EXPLANATION

For If function:

Logical_test: COUNTIF($B$2:B2,B2)=1

value_if_true: SUMIF($B$2:$B$10,B2,$C$2:$C$10)

value_if_false: “” – nothing returns

——————————————————————————————————————–

For COUNTIF function:

Range: $B$2:B2

Criteria: B2

For range $B$2:B2 is an expanding range. Start cell is B2, and the end cell is not fixed.

Select range and criteria separately in the formula bar, and press F9 to convert cell reference to real values. See screenshot below:

How to Subtotal Values for Groups and Only Keep One Subtotal for A Group in Column10

——————————————————————————————————————–

For SUMIF function:

Range: $B$2:$B$10

Criteria: B2

Sum Range: $C$2:$C$10

Select above arguments, press F9 to convert cell reference to real values. See screenshot below:

How to Subtotal Values for Groups and Only Keep One Subtotal for A Group in Column11

HOW THIS FORMULA WORKS

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

Refer to above steps, the formula is converted to below format in the formula bar.

=IF(COUNTIF("T-shirt (Red)","T-shirt (Red)")=1,SUMIF({"T-shirt (Red)";"T-shirt (Red)";"T-shirt (Red)";"T-shirt (Blue)";"T-shirt (Blue)";"T-shirt (Blue)";"T-shirt (Black)";"T-shirt (Black)";"T-shirt (Black)"},"T-shirt (Red)",{100;150;200;160;170;180;150;200;220}),"")

——————————————————————————————————————–

In IF function, the core part is logical test part, its returned value determines which branch we will go to.

Logical_test:

COUNTIF("T-shirt (Red)","T-shirt (Red)")=1

For this part, range “T-shirt (Red)” just contains one criteria “T-shirt (Red)”, so COUNTIF(“T-shirt (Red)”,”T-shirt (Red)”)=1, the formula COUNTIF(“T-shirt (Red)”,”T-shirt (Red)”)=1 is ‘True’. So we go to ‘value_if_ture’ and ignore ‘value_if_false’.

value_if_true:

SUMIF({"T-shirt (Red)";"T-shirt (Red)";"T-shirt (Red)";"T-shirt (Blue)";"T-shirt (Blue)";"T-shirt (Blue)";"T-shirt (Black)";"T-shirt (Black)";"T-shirt (Black)"},"T-shirt (Red)",{100;150;200;160;170;180;150;200;220})

For this part, we have one pair of criteria range and criteria:

criteria range:

{"T-shirt (Red)";"T-shirt (Red)";"T-shirt (Red)";"T-shirt (Blue)";"T-shirt (Blue)";"T-shirt (Blue)";"T-shirt (Black)";"T-shirt (Black)}

criteria:

"T-shirt (Red)"

Compare each value in the array with criteria, we get a new array that only contains ‘True’ and ‘False’.

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

Convert True to 1, False to 0.

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

Now, we have below two arrays in the formula:

Sum Range:

{100;150;200;160;170;180;150;200;220}

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

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 450 at last.

How to Subtotal Values for Groups and Only Keep One Subtotal for A Group in Column12

NOTE

We noticed that after typing the formula and dragging the handle down to fill the ‘Subtotal’ column, not all the cells have a sum value, some cells keep blank as we expect. Why the formula returns an empty value after coping it to other cells?

In cell D3, the formula is automatically adjusted to =IF(COUNTIF($B$2:B3,B3)=1,SUMIF(Product,B3,Amount),””) after coping and pasting.

How to Subtotal Values for Groups and Only Keep One Subtotal for A Group in Column13

In logical test part, CONTIF($B$2:B3,B3) is equal to COUNTIF({“T-shirt (Red)”;”T-shirt (Red)”},”T-shirt (Red)”).

How to Subtotal Values for Groups and Only Keep One Subtotal for A Group in Column14

Obviously, this formula returns 2, which is not equal to 1, so IF function returns ‘valus_if_false’ value, which is “” an empty value in this case. Above all, in D3, the subtotal is blank.

SUMMARY

1. To subtotal values we can apply different functions together. In this case we apply IF function core formula, and a formula contains COUNTIF function as logical test, SUMIFS function as ‘True’ part returned value.

2. For above three functions, they all allow logical operators like “>”,”>”,”<>” and wildcards like “*”,”?”.

Related Functions


  • Excel IF function
    The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
  • Excel COUNTIF function
    The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…
  • 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], …)…
  • Excel SUM function
    The Excel SUM function will adds all numbers in a range of cells and returns the sum of these values. You can add individual values, cell references or ranges in excel.The syntax of the SUM function is as below:= SUM(number1,[number2],…)
  • Excel SUBTOTAL function
    The Excel SUBTOTAL function returns the subtotal of the numbers in a list or database. The syntax of the SUBTOTAL function is as below:= SUBTOTAL (function_num, ref1, [ref2])….

 

 

 

How to Sum by Formula If Cells Are Not Blank in Criteria Range

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

How to Sum by Formula If Cells Are Not Blank in Criteria1

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’.

How to Sum by Formula If Cells Are Not Blank in Criteria2

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

How to Sum by Formula If Cells Are Not Blank in Criteria3

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

How to Sum by Formula If Cells Are Not Blank in Criteria4

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.

How to Sum by Formula If Cells Are Not Blank in Criteria5

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

How to Sum by Formula If Cells Are Not Blank in Criteria6

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.

How to Sum by Formula If Cells Are Not Blank in Criteria7

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.

How to Sum by Formula If Cells Are Not Blank in Criteria8

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 to Sum by Formula If Cells Are Not Blank in Criteria9

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.

How to Sum by Formula If Cells Are Not Blank in Criteria10

COMMENTS

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

How to Sum by Formula If Cells Are Not Blank in Criteria11

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

How to Sum by Formula If Cells Are Not Blank in Criteria12

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).

How to Sum by Formula If Cells Are Not Blank in Criteria13

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])…

 

 

How to Sum by Formula if Cell Ends with in Excel

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

How to Sum by Formula if Cell Ends with in Excel1

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’.

How to Sum by Formula if Cell Ends with in Excel2

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

How to Sum by Formula if Cell Ends with in Excel3

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″).

How to Sum by Formula if Cell Ends with in Excel4

Step 4: Press Enter after typing the formula.

How to Sum by Formula if Cell Ends with in Excel5

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.

How to Sum by Formula if Cell Ends with in Excel6

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:

How to Sum by Formula if Cell Ends with in Excel7

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”.

How to Sum by Formula if Cell Ends with in Excel8

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”.

How to Sum by Formula if Cell Ends with in Excel9

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).

How to Sum by Formula if Cell Ends with in Excel10

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.

How to Sum by Formula if Cell Ends with in Excel11

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

How to Sum by Formula if Cell Ends with in Excel12

SUMIF – CRITERIA

How to Sum by Formula if Cell Ends with in Excel13

SUMIF – SUM RANGE

How to Sum by Formula if Cell Ends with in Excel14

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])…

 

How to Sum if Less Than A Number in Excel

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])…

 

 

How to Sum if Greater Than A Number in Excel

Sometimes we may meet the case that to sum numbers with the pre-condition “greater than X” exists. We only want to sum numbers which are greater 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

How to Sum if Greater Than A Number in Excel 1

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 of “how many students get this score in an exam”.  In the right-hand table, it is a simple summary, there is one criterion that “score is greater than 70”, and the expectation is counting the total number of students whose score is greater than 70. Actually 70 is what we just entered into E2, we can also change it to another value.

In this instance, we want to sum numbers from “Count” column based on the criteria “Score > 70 (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’.

How to Sum if Greater Than A Number in Excel 2

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

How to Sum if Greater Than A Number in Excel 3

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).

How to Sum if Greater Than A Number in Excel 4

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.

How to Sum if Greater Than A Number in Excel 5

Step 4: Press Enter after typing the formula.

How to Sum if Greater Than A Number in Excel 6

In column A, cell A6 (75), A8 (80) and A9 (85) are greater than 70, the corresponding counts are 2 (in cell B6), 4 (in cell B8), and 2 (in cell B9), so the total count is 2+4+2=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

In this instance, we need to count numbers of student whose score is greater than 70. We need to compare each score in “Score” column with 70, so “A2:A9” is the ‘range’.

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

How to Sum if Greater Than A Number in Excel 7

SUMIF – CRITERIA

We enter “>”&E2 into criteria part. As we mention above, if logical operator is used, it should be quote with double quotes “”; In E2 we supply a certain value “70” to filter scores; to concentrate logical operator “>” and number “70”, we must to use a special character “&” to connect them, if “&” is missing, formula quits directly.

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

How to Sum if Greater Than A Number in Excel 8

SUMIF – SUM RANGE

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

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

How to Sum if Greater Than A Number in Excel 9

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},”>70″,{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: “>70”

Compare each number in array with 70; if number can satisfy the condition “>70”, mark it bold in array:

{60;65;70;50;75;55;80;85} – Bold if it is >70

Now, for bold numbers, as they can meet our requirement, so record a ‘True’ for them in the array; otherwise, record a ‘False’ for others.

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

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

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

Now, we have below two arrays:

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

{0;0;0;0;1;0;1;1} – from above steps we know that if number is greater 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.

How to Sum if Greater Than A Number in Excel 10

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

2+4+2=8

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 1: In E3, enter the formula =SUMIFS(Count,Score,”>”&E2).

How to Sum if Greater Than A Number in Excel 11

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 2: Press Enter after typing the formula.

How to Sum if Greater Than A Number in Excel 12

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

How to Sum if Greater Than A Number in Excel 13

SUMIFS – CRITERIA RANGE

How to Sum if Greater Than A Number in Excel 14

SUMIFS – CRITERIA

How to Sum if Greater Than A Number in Excel 15

SUMIFS WORKFLOW

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

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])…

How to Sum if Date is Greater Than A Date in Excel

In previous article, we have introduced you the method to sum if date is included in a period, you can check “How to Sum if Date Between Two Dates” on our website for reference. But sometimes we may want to sum numbers not for a period but just after a date. In this article, we will show you how to ‘sum if date is greater than a date’ by formula with the help of Excel SUMIF/SUMIFS function. We also introduce DATE function in case of date format is not a valid date format so that it cannot be used in a formula directly.

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

EXAMPLE

How to Sum if Date is Greater Than A Date in Excel 1

Refer to above left-hand table, we can see that in the most left column “Date”, some dates are listed. And amount for each date is listed in “Amount” column accordingly.

In this instance, we want to sum numbers after a certain date. And this date is saved in “After Date” field in the right-hand table. This date is a dynamic date and we can change it properly based on our requirement. After the date is confirmed, only the dates which are later than the date can meet our condition. We need to find out all proper dates, and then sum up values in “Amount” column. To resolve this issue by formula, we can apply SUMIF or SUMIFS.

FORMULA – SUMIF FUNCTION

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

How to Sum if Date is Greater Than A Date in Excel 2

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

How to Sum if Date is Greater Than A Date in Excel 3

Step 3: In E2, enter the formula =SUMIF(Date,”>”&DATE(2021,4,15),Amount).

How to Sum if Date is Greater Than A Date in Excel 4

NOTE: In step#1 and step#2 we defined range name “Date” 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. Be aware that don’t select DATE function in the dropdown list.

How to Sum if Date is Greater Than A Date in Excel 5

Step 4: Press Enter after typing the formula.

How to Sum if Date is Greater Than A Date in Excel 6

We can see in column A, cell A7 (4/18/2021), A8 (5/5/2021) and A9 (6/6/2021) are after date “4/15/2021”, so the corresponding amounts are 230 (in cell B7), 150 (in cell B8), and 250 (in cell B9), so the total amount is 230+150+250=630. The formula works correctly.

FORMULA – SUMIFS FUNCTION

We can also apply SUMIFS function to resolve this issue.

Step 1: In E2, enter the formula =SUMIFS(Amount,Date,”>”&DATE(2021,4,15)).

How to Sum if Date is Greater Than A Date in Excel 7

Step 2: Press Enter after typing the formula.

How to Sum if Date is Greater Than A Date in Excel 8

We can see that the difference between SUMIF and SUMIFS is argument order. Compare with SUMIF, SUMIFS function list sum range at first, then criteria range, and criteria lists in the end. Besides, SUMIFS function allows multiple “criteria range and criteria” combinations.

FUNCTION INTRODUCTION

1. SUMIF Function

SUMIFS function can be seen as SUM+IF, it can sum based on 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.

2. SUMIFS Function

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.

Both SUMIF and SUMIFS function allow wildcards like asterisk ‘*’ and question mark ‘?’, they also allow logical operators within 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. “>=” – greater than or equal to
  3. “<” – less than
  4. “<” – less than or equal to
  5. “<>” – not equal to
  6. To connect logical operator and text, we need to add an ampersand “&” to concentrate them. For example “>” & 100 or “<=” & E2.
  7. DATE Function

DATE function can return a serial number that can represent a particular date if current cell format is ‘General’ or directly return a date if current cell format is ‘Date’. The syntax is

DATE(year,month,day) – All the three arguments ‘year’,’month’,’day’ are required.

ARGUMENTS

Let’s introduce the arguments of used functions.

DATE – YEAR, MONTH, DAY

If date needs to be hard coded in a formula, we often use DATE function. In this case, date is 15-Apr-2021, so year=2021, month=4, day=15.

In the formula bar, select ‘”>”&Date(2021,4,15)’, press F9, date is converted to a five digits number in the formula.

How to Sum if Date is Greater Than A Date in Excel 9

In this case, SUMIF and SUMIFS have the same sum range, criteria range and criteria references, and working processes are similar, so we only clarify SUMIFS function in the following parts.

SUMIFS – SUM RANGE

In our instance, B2:B9 is the ‘sum range’. 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.

How to Sum if Date is Greater Than A Date in Excel 10

SUMIFS – CRITERIA RANGE

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

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

How to Sum if Date is Greater Than A Date in Excel 11

In this step, dates are converted from Date format to General format, a five digits number is displayed to represent a date. Date 12/30/2020 in A2 is the earliest date in the list, so its relative five digits number 44195 is the smallest in the array, and date 6/6/2021 is the latest date in the list, so 44353 is the largest among all numbers in the array.

SUMIFS – CRITERIA

We want to calculate total amount after date “4/15/2021”. As in the table ‘’ is not a valid date, so we cannot directly use cell reference in the formula, so DATE function is applied here. As the target date should be after “4/15/2021”, so “>” is added. The complete criteria is “>”&DATE(2021,4,15).

How to Sum if Date is Greater Than A Date in Excel 12

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},{44195;44227;44228;44252;44298;44304;44321;44353},”>44301″)

We have below pair of criteria range and criteria:

Criteria range: {44195;44227;44228;44252;44298;44304;44321;44353}

Criteria: “>44301”

In criteria range, compare each number in the array with criteria 44301; if number can satisfy the condition “>44301”, mark it bold in array:

{44195;44227;44228;44252;44298;44304;44321;44353} – Bold if it is >44301

Now, for bold numbers, they can meet our requirement, so record a ‘True’ for them in the array; otherwise, record a ‘False’ for others. Then we can get a new array:

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

Convert ‘True’ to ‘1’ and ‘False’ to ‘0’ to apply this array into calculation in the following steps:

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

Now, we have below two arrays:

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

{0;0;0;0;0;1;1;1} – from above steps we know that if date is after “4/15/2021”, 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.

How to Sum if Date is Greater Than A Date in Excel 13

Sum up all numbers in the third row.

How to Sum if Date is Greater Than A Date in Excel 14

230+150+250=630

COMMENT

1.If “After Date” value is changed in E2, we need to update DATE function year, month, date values manually.

How to Sum if Date is Greater Than A Date in Excel 15

2.If date is a valid date like 4/15/2021, we can directly use cell reference in the formula. See example below.

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.

If you want to hard-code date in formula, you can apply DATE function.

 

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 DATE function
    The Excel DATE function returns the serial number for a date.The syntax of the DATE function is as below:= DATE (year, month, day)…
  • 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])…

 

How to Sum if Date Between Two Dates in Excel

In previous article, we have introduced you the method to sum if by year, you can check “How to Sum Data if by Year in Excel” on our website for reference. But sometimes we may want to sum numbers not for a year but just for a period. In this article, we will show you how to ‘sum if date between two dates’ by formula with the help of Excel SUMIFS function. Due to date format issue may occurs in real life, we also introduce DATE function in case of you meet the same problem as we describe in example2.

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

EXAMPLE

How to Sum by Formula if Cell Between Two Dates 1

Refer to above left-hand table, we can see that in the most left column “Date”, some dates are listed. And amount for each date is listed in “Amount” column accordingly.

In this instance, we want to sum numbers between a period. And this period is confirmed by the two dates “Start Date” and “End Date” in the right-hand table. This period is a dynamic period due to we can change “Start Date” and “End Date” based on our requirement. After the period is provided, only the dates which are between this period can meet our condition. We need to find out all proper dates, and then sum up values in “Amount” column. To resolve this issue by formula, we can apply SUMIFS functions.

FORMULA – SUMIFS FUNCTION

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

How to Sum by Formula if Cell Between Two Dates 2

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

How to Sum by Formula if Cell Between Two Dates 3

Step 3: In E4, enter the formula =SUMIFS(Amount,Date,”>”&E2,Date,”<” & E3).

How to Sum by Formula if Cell Between Two Dates 4

NOTE: In step#1 and step#2 we defined range name “Date” 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.

How to Sum by Formula if Cell Between Two Dates 5

Step 4: Press Enter after typing the formula.

How to Sum by Formula if Cell Between Two Dates 6

We can see in column A, cell A5 (2/25/2021) and A6 (4/12/2021) just between period “2/25/2021-4/15/2021”, the corresponding amounts are 150 (in cell B5), 130 (in cell B6), and 150 (in cell B8), so the total amount is 150+130=280. 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. “>=” – greater than or equal to
  3. “<” – less than
  4. “<” – less than or equal to
  5. “<>” – 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.

How to Sum by Formula if Cell Between Two Dates 7

SUMIFS – CRITERIA RANGE 1

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

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

How to Sum by Formula if Cell Between Two Dates 8

In this step, dates are converted from Date format to General format, a five digits number is displayed to represent a date. Date 12/30/2020 in A2 is the earliest date in the list, so its relative five digits number 44195 is the smallest in the array, and date 6/6/2021 is the latest date in the list, so 44353 is the largest among all numbers in the array.

SUMIFS – CRITERIA 1

We want to calculate total amount for the period between start date “2/15/2021” and end date “4/15/2021”. The two dates are saved in E2 and E3, we can directly enter cell reference in the formula to stand for the two dates. As the target date should be included in the two dates, that means it is greater than the start date (>E2) and at the mean time it is also smaller than the end date (<E3).

Thus, the first criteria should be “greater than the date in E2”. As we explained above, SUMIFS function allows logical operators, but they should be enclosed into double quotes “”, so for criteria 1, we enter logical operator > within quotes as “>”. And to concentrate logical operator and cell reference E2, we add & between them. Please be aware that if you enter “>”E2 without & into formula, formula cannot accept this combination and directly quit entering. After all, for criteria 1, we enter “>”&E2.

E2=2/15/2021, convert it to general format in the formula bar, it is 44242.

How to Sum by Formula if Cell Between Two Dates 9

SUMIFS – CRITERIA RANGE 2

It is still range A2:A9.

How to Sum by Formula if Cell Between Two Dates 10

SUMIFS – CRITERIA 2

The second criteria should be “smaller than the date in E3”. So we enter “<”&E3 in the formula.

E3=4/15/2021, convert it to general format in the formula bar, it is 44301.

How to Sum by Formula if Cell Between Two Dates 11

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},{44195;44227;44228;44252;44298;44304;44321;44353},”>”&44242,{44195;44227;44228;44252;44298;44304;44321;44353},”<“&44301)

See screenshot below:

How to Sum by Formula if Cell Between Two Dates 12

See the two pairs of criteria range and criteria:

Criteria range 1: {44195;44227;44228;44252;44298;44304;44321;44353}

Criteria 1: “>”&44242

In criteria range 1, compare each number in the array with criteria 44242; if number can satisfy the condition “>44242”, mark it bold in array:

{44195;44227;44228;44252;44298;44304;44321;44353} – Bold if it is >44242

Criteria range 2: {44195;44227;44228;44252;44298;44304;44321;44353}

Criteria 2: “<“&44301

In criteria range 2, compare each number in the array with criteria 44301; if number can satisfy the condition “<44301”, mark it bold in array:

{44195;44227;44228;44252;44298;44304;44321;44353} – Bold if it is <44301

Find out the intersection between two arrays. The result can meet our requirement “between 44242 and 44301” which means “between the period 2/15/2021 and 4/15/2021”.

{44195;44227;44228;44252;44298;44304;44321;44353} – Bold if it is just between 44242 and 44301.

Now, for bold numbers, they can meet our requirement, so record a ‘True’ for them in the array; otherwise, record a ‘False’ for others. Then we can get a new array:

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

Convert ‘True’ to ‘1’ and ‘False’ to ‘0’ to apply this array into calculation in the following steps:

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

Now, we have below two arrays:

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

{0;0;0;1;1;0;0;0} – from above steps we know that if date is included in the period, 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.

How to Sum by Formula if Cell Between Two Dates 13

Sum up all numbers in the third row.

150+130=280

EXAMPLE 2

Sometimes dates are not just displayed in proper date format which can be directly applied in formula as we expect. They can be displayed in some other date formats in worksheet, see example below:

How to Sum by Formula if Cell Between Two Dates 14

In this situation, if you want to sum numbers based on period, you should hard code dates by DATE function, then refer to above method to apply SUMIFS function.

FORMULA – SUMIFS AND DATE FUNCTIONS

Step 1: In E4, enter the formula =SUMIFS(Amount,Date,”>”&DATE(2021,2,15),Date,”<“&DATE(2021,4,15)).

How to Sum by Formula if Cell Between Two Dates 15

Step 2: Press Enter after typing the formula.

How to Sum by Formula if Cell Between Two Dates 16

DATE FUNCTION INTRODUCTION

DATE function can return a serial number that can represent a particular date if current cell format is ‘General’ or directly return a date if current cell format is ‘Date’. The syntax is

DATE(year,month,day) – All the three arguments ‘year’,’month’,’day’ are required.

HOW FORMULA WORKS

 

We applied DATE function twice in this case, the start date is 15-Feb-2021, end date is 15-Apr-2021, so in the first DATE function we enter DATE(2021,2,15), and the second DATE function we enter DATE(2021,4,15).

In the formula bar, select “DATE(2021,2,15)”, press F9, date is converted to a five digits number 44242.

How to Sum by Formula if Cell Between Two Dates 17

Then select “DATE(2021,2,15)”, press F9, date is converted to a five digits number 44301.

How to Sum by Formula if Cell Between Two Dates 18

Now, we get the two numbers, the following steps are as same as example1.

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.

If you want to hard-code date in formula, you can apply DATE function.

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 DATE function
    The Excel DATE function returns the serial number for a date.The syntax of the DATE function is as below:= DATE (year, month, day)…

 

 

How to Sum by Formula if Cell Contains Special Character in Excel

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

How to Sum by Formula if Cell Contains Special Character 1

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’.

How to Sum by Formula if Cell Contains Special Character 2

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

How to Sum by Formula if Cell Contains Special Character 3

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

How to Sum by Formula if Cell Contains Special Character 4

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.

How to Sum by Formula if Cell Contains Special Character 5

Step 4: Press Enter after typing the formula.

How to Sum by Formula if Cell Contains Special Character 6

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.

How to Sum by Formula if Cell Contains Special Character 7

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.

How to Sum by Formula if Cell Contains Special Character 8

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 to Sum by Formula if Cell Contains Special Character 9

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:

How to Sum by Formula if Cell Contains Special Character 10

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.

How to Sum by Formula if Cell Contains Special Character 11

Sum up all numbers in the third row.

How to Sum by Formula if Cell Contains Special Character 12

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).

How to Sum by Formula if Cell Contains Special Character 13

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], …)…

 

 

How to Sum by Formula if Cell Contains Both A and B in Excel

In previous articles, we have introduced the method of sum numbers for cells which are equal to a certain value or are not equal to a certain value (you can refer to ‘How to Sum if Cell Contains Text in Another Column’, ‘How to Sum Numbers by Formula if Cells Are Not Equal to Certain Value’ on our website). In this article, we will show you the how to ‘sum if cell contains both A and B’ 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 to reach your goal. After reading the article, you may have a simple understanding of SUMIFS function.

EXAMPLE

How to Sum by Formula if Cell Contains Both A and B in Excel 1

Refer to above table, we can see that some kinds of fruit are listed in “Fruit” column. At least two fruits are displayed in one cell. Total amount for fruit combination is listed in “Amount” column accordingly.

In this instance, we want to calculate total amount for fruit combination that contains “Apple” and “Orange”. So, no matter “Apple, Orange” or “Apple, Orange, Grape”, they are both seem to match the criterion. We need to find out all proper fruit combination, and then sum up values in “Amount” column. To fix this issue by formula, we can apply SUMIFS function here.

FORMULA – SUMIF FUNCTION

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

How to Sum by Formula if Cell Contains Both A and B in Excel 2

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

How to Sum by Formula if Cell Contains Both A and B in Excel 3

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

How to Sum by Formula if Cell Contains Both A and B in Excel 4

Step 4: Press Enter after typing the formula.

How to Sum by Formula if Cell Contains Both A and B in Excel 5

We can see in column A, cell A2, A4 and A8 contains both “Apple” and “Orange”, 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.

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.

How to Sum by Formula if Cell Contains Both A and B in Excel 6

SUMIFS – CRITERIA RANGE 1

A2:A9 is the criteria range. In this instance we have only one criteria range. This range contains several kinds of fruit.

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

How to Sum by Formula if Cell Contains Both A and B in Excel 7

SUMIFS – CRITERIA 1

We want to calculate total amount for combinations contain “Apple” and “Orange”, so we will supply two criteria in this case, the first one is “Apple”. As there might be some characters listing before or after “Apple”, so we need to add “*” before and after “Apple”. As SUMIFS function supports texts and wildcards, but they should be enclosed into double quotes “ “ when applying them, so for criteria 1, we enter “*Apple*” in this position.

How to Sum by Formula if Cell Contains Both A and B in Excel 8

SUMIFS – CRITERIA RANGE 2

We have only one criteria range. So in criteria range2 position, we still enter “Amount”.

How to Sum by Formula if Cell Contains Both A and B in Excel 9

SUMIFS – CRITERIA 2

In criteria 1 we enter “*Amount*”, so in criteria 2 we enter “*Orange*”. Then the two criteria are both included in this formula.

How to Sum by Formula if Cell Contains Both A and B in Excel 10

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, we can get three arrays and two texts within double quotes, the formula is converted into below format in the formula bar.

=SUMIFS({100;120;200;150;130;230;150;250},{“Apple, Orange, Grape”;”Banana, Orange”;”Orange, Apple”;”Apple, Banana”;”Banana, Orange, Grape”;”Orange, Banana”;”Apple, Grape, Orange”;”Banana, Grape”},”*Apple*”,{“Apple, Orange, Grape”;”Banana, Orange”;”Orange, Apple”;”Apple, Banana”;”Banana, Orange, Grape”;”Orange, Banana”;”Apple, Grape, Orange”;”Banana, Grape”},”*Orange*”)

See screenshot below:

How to Sum by Formula if Cell Contains Both A and B in Excel 11

There are two pairs of criteria range and criteria:

Criteria Range 1: {“Apple, Orange, Grape”;”Banana, Orange”;”Orange, Apple”;”Apple, Banana”;”Banana, Orange, Grape”;”Orange, Banana”;”Apple, Grape, Orange”;”Banana, Grape”}

Criteria 1: “*Apple*”

Criteria Range 2: {“Apple, Orange, Grape”;”Banana, Orange”;”Orange, Apple”;”Apple, Banana”;”Banana, Orange, Grape”;”Orange, Banana”;”Apple, Grape, Orange”;”Banana, Grape”}

Criteria 2: “*Orange*”

In criteria range 1, compare each fruit combination in the array with criterion “Apple”; if “Apple” exists in fruit combination, mark them in bold:

{“Apple, Orange, Grape“;”Banana, Orange”;”Orange, Apple“;”Apple, Banana“;”Banana, Orange, Grape”;”Orange, Banana”;”Apple, Grape, Orange“;”Banana, Grape”}

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

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

In criteria range 2, compare each fruit combination in the array with criterion “Orange”; if “Orange” exists in fruit combination, mark them in bold:

{“Apple, Orange, Grape“;”Banana, Orange“;”Orange, Apple“;”Apple, Banana”;”Banana, Orange, Grape“;”Orange, Banana“;”Apple, Grape, Orange“;”Banana, Grape”}

Then we get another array:

Array 2: {True;True;True;False;True;True;True;False}

As the two criteria “Apple” and “Orange” both should be included in the fruit combination, so we need to look up the intersection of array1 and array2, only True is marked in the same position in two arrays, True will keep in array. So, after comparing the two arrays, we get a new array3:

Array 3: {True;False;True;False;False;False;True;False}

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

Array 4: {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 4: {1;0;1;0;0;0;1;0} – from above steps we know that if cell contains both “Apple” and “Orange”, 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 sum total.

Above all, numbers which are not zero in sum total row are the amounts of cell contains both ‘Apple’ and ‘Orange’. Now, sum up all numbers in sum total.

How to Sum by Formula if Cell Contains Both A and B in Excel 12

100+200+150=450

COMMENTS

In this case, if we forgot to add “*” in criteria, let’s see what do we get.

How to Sum by Formula if Cell Contains Both A and B in Excel 13

In fact, an asterisk (*) means one or more characters, if * is omit in criteria, that means cell which is exactly equal to the criteria can satisfy the condition. As we all know, a cell cannot either be ‘Apple’ or ‘Orange’. So, if we enter =SUMIFS(Amount,Fruit,”Apple”,Fruit,”Orange”) to sum numbers, it doesn’t work.

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], …)…

 

 

How to Sum Numbers by Formula if Cells Are Not Equal to Certain Value in Excel

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

How to Sum Numbers by Formula if Cells Are Not Equal to Certain Value 1

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’.

How to Sum Numbers by Formula if Cells Are Not Equal to Certain Value 2

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

How to Sum Numbers by Formula if Cells Are Not Equal to Certain Value 3

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

How to Sum Numbers by Formula if Cells Are Not Equal to Certain Value 4

Step 4: Press Enter after typing the formula.

How to Sum Numbers by Formula if Cells Are Not Equal to Certain Value 5

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.

How to Sum Numbers by Formula if Cells Are Not Equal to Certain Value 6

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 “ “.

How to Sum Numbers by Formula if Cells Are Not Equal to Certain Value 7

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 to Sum Numbers by Formula if Cells Are Not Equal to Certain Value 8

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:

How to Sum Numbers by Formula if Cells Are Not Equal to Certain Value 9

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:

How to Sum Numbers by Formula if Cells Are Not Equal to Certain Value 10

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.

How to Sum Numbers by Formula if Cells Are Not Equal to Certain Value 11

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], …)…