There is a wide range of suitable methods to sum up a range of cells based on column and row criteria in MS Excel. In this article, I’ll try to illustrate how you can use a number of appropriate functions or formulas related to summing up the cells under column and row criteria.

**Download Practice Workbook**

You can download the Excel workbook that we’ve used to prepare this article.

## 7 Handy Approaches to Sum Based on Column and Row Criteria in Excel

**1. Using SUMPRODUCT to Sum Based on Column and Row Criteria**

We have a dataset where the sales of computers of different brands have been recorded over 6 months. In this section at first, we’ll find out the number of HP devices sold in January. We can do this with the help of the **SUMPRODUCT** function. The syntax of this function is-

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

**SUMPRODUCT** function returns the sum of the products of corresponding ranges or arrays.

**📌**** Steps:**

➤ Based on our dataset, we have to type in **Cell L10**–

`=SUMPRODUCT((D5:I14)*(D4:I4="Jan")*(B5:B14="HP"))`

➤ After pressing **Enter**, you’ll find a total of 23 HP devices have been sold in January.

**🔎**** How Does This Formula Work?**

➤ In the arguments of the function, **D5:I14 **is the range of cells that is to be considered for our calculation.

➤ By typing **D4:I4=”Jan”** as well as B**5:B14=”HP”** we’re assigning the criteria along row & column in the function.

**2. Combining SUM & IF Functions to Sum under Column and Row Criteria**

We can also combine **SUM** & **IF** functions together to find a similar output. Before getting down to the function, let’s have a glance at the syntaxes of **SUM** & **IF** functions.

**Syntax of SUM:**

**=SUM(number1,[number2],….)**

**Function of SUM:**

Adds all the numbers in a range of cells

**Syntax of IF:**

`=IF(logical_text,[value if true],[value if false])`

**Function of IF:**

Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE.

**📌**** Steps:**

➤ In **Cell L10**, type-

`=SUM(IF(D4:I4=L9,IF(B5:B14=L8,D5:D14)))`

➤ Press **Enter** & the function will return as 23.

**🔎**** How Does This Formula Work?**

➤ By combining two **IF** functions in the arguments, the formula goes through all the sales recorded in the chart & returns as** {8,15, FALSE, FALSE…}.**

➤ **SUM** function then sums up the number values to **23 (8+15)**.

**3. Applying SUMIF Function to Sum Based on Column and Row Criteria**

**i) Using SUMIF with Multiple AND Criteria Based on Data in Columns and Rows**

**SUMIF** is another useful function that we can use to evaluate the previous result. This function adds the cells specified by a given condition or criteria. The syntax of the **SUMIF** function is-

`=SUMIF(range, criteria, [sum_range])`

**📌**** Steps:**

➤ Type in** Cell L10**:

`=SUMIF(B5:B14,L8,D5:D14)`

➤ Press** Enter** & you’re done.

**ii) Using SUMIF with Multiple OR Criteria Based on Data in Columns and Rows**

Now we want to add more conditions to the functions. We’ll find out the number of HP as well as Lenovo devices sold in January. We can use two **SUMIF** functions here for two different criteria & then simply add the resultant values.

**📌**** Steps:**

➤ In **Cell L10**, we have to type:

`=SUMIF(B5:B14,L7,D5:D14) + SUMIF(B5:B14,L8,D5:D14)`

➤ Press **Enter** & you’ll see the resultant value as 42.

**4. Incorporating SUMIF, INDEX & MATCH Functions Together to Sum under Column and Row Criteria**

By using** INDEX & MATCH **functions we can find out the result more precisely. Before going to the steps, let’s have a look at how these **INDEX & MATCH** functions work.

**Syntax of INDEX:**

`=INDEX(array, row_num, [column_num])`

or,

`=INDEX(referencem row_num, [column_num], [area_num])`

**Function of INDEX:**

Returns a value or reference of the cell at the intersection of the particular row & column, in a given range.

**Syntax of MATCH:**

`=MATCH(lookup_value, lookup_array, [match_type])`

**Function of the MATCH:**

Returns the relative position of an item in an array that matches a specified value in a specified order.

**📌**** Steps:**

➤ As we’re going to find out the number of sales of HP devices in January once again, we have to type in **Cell L10:**

`=SUMIF(B5:B14,L8,INDEX(D5:I5,MATCH(L9,D4:I4,0)))`

➤ Now press** Enter** & you’ll get the resultant value as 23.

**🔎**** How Does This Formula Work?**

➤ **MATCH** function looks for the position of the month ‘**Jan’** in the range of cells **D4:I4**.

➤ **INDEX** function then stores all the sales in the column of ‘**Jan’** as reference.

➤ **SUMIF** function finally sums up the sales of HP devices only.

**5. Using SUMIFS to Sum Based on Column and Row Criteria**

**i) Using SUMIFS to SUM under Multiple AND Criteria Based on Column and Row**

**SUMIFS** is the sub-category of **SUMIF** function which adds the cells specified by a given set of conditions or criteria & we can use this function to add multiple criteria in a single function. We don’t need to type two different functions to sum in the function bar. The syntax of this function is-

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

Now with the help of this function, we want to know the number of sales of HP notebooks in January.

**📌**** Steps:**

➤ In **Cell L10**, type:

`=SUMIFS(D5:D14, B5:B14, L8, C5:C14,L9)`

➤ Press **Enter** & you’ll get the return value, 15.

**ii) Using SUMIFS with Multiple OR Criteria to Sum Based on Data Columns and Rows**

Now we’ll deal with more criteria & so we can add the **SUMIFS** function too for obtaining the desired output. From our dataset, we want to know the total number of sales of HP notebooks in January and Lenovo desktops in April together.

**📌**** Steps:**

➤ Based on our criteria, the formula in **Cell L11** will be:

`=SUMIFS(D5:D14,B5:B14,L9,C5:C14,L10)+SUMIFS(G5:G14,B5:B14,M9,C5:C14,M10)`

➤ Press **Enter **& you’ll obtain the total count as 18.

**iii) Using SUMIFS to Sum Based on Column and Row Criteria with Dates**

We can input dates criteria also in our functions. Our dataset is now quite different from the previous one. A computer shop has sold a number of devices & there are dates of purchase, dates of delivery and the prices of the devices are recorded in the table. We want to know the total payments the shop will receive after delivering the rest of the products (purchased after May) from the current date **(While preparing this section of the article, the current date was 7/8/2021)**.

**📌**** Steps:**

➤ In **Cell I16**, we have to type:

`=SUMIFS(F5:F27,D5:D27,">=6/1/2021",E5:E27,">="&TODAY())`

➤ Press **Enter **& you’ll find the total payments as $5140 to be received by the shop after deliveries.

**iv) Inserting SUMIFS to Sum under Column and Row Criteria with Blank & Non-Blank Cells**

We can also deal with blank as well as non-blank cells while summing a range of data based on multiple criteria with **SUMIFS** function. In our dataset now, there are a few missing values in the columns of **Date of Purchase** & **Date of Delivery**. We want to know the total price of the products that have been purchased but not been delivered yet.

**📌**** Steps:**

➤ In **Cell I16**, our formula for the given criteria will be:

`=SUMIFS(F5:F27,D5:D27,"<>",E5:E27,"=")`

➤ Press **Enter** & you’ll find a total of $3320 to be received after deliveries of the rest of the products or devices.

We’re using **“<>”** to exclude the blank cells & inserting **“=”** as another criterion that denotes the inclusion of blank cells.

**6. Using Array Formula to Sum Based on Column and Row Criteria**

In this section, we’ll determine the total number of sales in Juen for HP, Lenovo & Asus devices. And we can do it by using the array formula too.

**📌**** Steps:**

➤ In **Cell L10**, type:

`=SUM(SUMIF(B5:B14,{"HP","Lenovo","Asus"},I5:I14))`

➤ Press **Enter **& you’re done.

**Note:** While using curly brackets to include the names you cannot use cell references here. You have to use Double-Quotes to input the names inside the array.

**7. Fusing SUMPRODUCT & SUMIF Functions Together to Sum under Column and Row Criteria**

If you want to avoid typing the names & including the cell references, then you can insert **SUMPRODUCT** along with SUMIF functions to obtain the similar result.

**📌**** Steps:**

➤ In **Cell L10**, our formula will be:

`=SUMPRODUCT(SUMIF(B5:B14,L7:L9,I5:I14))`

➤ Press **Enter** & you’re done.

**Concluding Words**

I hope all these methods mentioned in the article will help you to use these functions in your regular Excel tasks. You can suggest in the comment section if you think I’ve missed a point or method that should have been added too. Or you can check out our other articles related to Excel functions on this website.