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.

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.

FORMULA – SUMIFS FUNCTIONS

Step 1: In C9, enter the formula

=SUMIFS(C$2:C$7,$B$2:$B$7,$B9).

How to Sum in Vertical Range 2

Step 2: 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.

Step 3: 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

FUNCTION INTRODUCTION

x
How to Show All Named Ranges in Excel

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

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

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

 

 

 

Related Posts

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

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

How to Sum if Equal to X or Y in Excel
How to Sum if Equal to X or Y 19

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

How to Sum if Contains an Asterisk
How to Sum in Last N Days 14

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

How to Sum if Contains an Asterisk
How to Sum if Contains an Asterisk 7

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

How to Sum in Horizontal Range
How to Sum in Horizontal Range9

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

How to Sum with Criteria and Or Logic in Excel
How to Sum with Criteria and Or Logic in Excel 8

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

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

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

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

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

How to Sum by Formula if Cell Ends with in Excel
How to Sum by Formula if Cell Ends with in Excel14

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

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

Sidebar