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

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

Step2: Press Enter after typing the formula.

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.

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.

Note: 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:$G$2,I$2,$B3:$G3), we can get the same result as applying SUMIFS.

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