How to Sum Based on Column and Row Criteria in Excel (7 Ways)

Get FREE Advanced Excel Exercises with Solutions!

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, we’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. Furthermore, for conducting the session we’re going to use Microsoft Office 365 version.


How to Sum Based on Column and Row Criteria in Excel: 7 Ways

Here, we’ll describe 7 handy methods to sum up based on column and row criteria in MS Excel. For your better understanding, we will use a sample dataset where the sales of computers of different brands have been recorded over 6 months. Which has 8 columns. They are Brand, Device Category, Jan, Feb, Mar, Apr, May, and Jun. The dataset is given below.

Dataset about how to sum based on column and row criteria


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

In this section 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],….)

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

📌 Steps:

  • Firstly, based on our dataset, we have to type in cell L10.
=SUMPRODUCT((D5:I14)*(D4:I4="Jan")*(B5:B14="HP"))

Using SUMPRODUCT Function to Sum Based on Column and Row Criteria

  • 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 B5:B14=”HP” we’re assigning the criteria along the row & column in the function.

Read More: How to Apply SUMIF with Multiple Ranges in Excel


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:

  • Firstly, in cell L10, type the formula below.
=SUM(IF(D4:I4=L9,IF(B5:B14=L8,D5:D14)))
  • Press ENTER & the function will return 23.

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

🔎 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…}.
  • Next, the SUM function then sums up the number values to 23 (8+15).

Read More: How to Sum Multiple Columns Based on Multiple Criteria in Excel


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

Here, the SUMIF function 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])

i) Using SUMIF with Multiple AND Criteria

Now, we will use the SUMIF function with multiple AND criteria.

📌 Steps:

  • Type in cell L10.
=SUMIF(B5:B14,L8,D5:D14)
  • Then, press ENTER.

As a result, you will get the Total Sales number for the HP brand in January.

Using SUMIF with Multiple AND Criteria


ii) Using SUMIF with Multiple OR Criteria

Now, we want to add more conditions to the functions. Basically, we’ll find out the number of HP as well as Lenovo devices sold in January. Here, we can use two SUMIF functions 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)
  • Next, press ENTER & you’ll see the resultant value as 42.

Using SUMIF with Multiple OR Criteria

Read More: How to SUMIF for Multiple Criteria Across Different Sheet in Excel


4. Incorporating SUMIF, INDEX & MATCH Functions in Excel

Here, by using INDEX & MATCH functions we can find out the result more precisely. But, 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(references 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 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 the cell L10:
=SUMIF(B5:B14,L8,INDEX(D5:I5,MATCH(L9,D4:I4,0)))
  • Now, press ENTER & you’ll get the resultant value.

Incorporating SUMIF, INDEX & MATCH Functions to sum based on column and row criteria in Excel

🔎 How Does This Formula Work?

  • Here, the MATCH function looks for the position of the month Jan in the range of cells D4:I4.
  • Then, the INDEX function stores all the sales in the column of Jan as a reference.
  • After that, the SUMIF function finally sums up the sales of HP devices only.

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

Now, we will use the SUMIFS function to sum up a range of cells based on column and row criteria in MS Excel. Here, the SUMIFS is the subcategory of the SUMIF function which adds the cells specified by a given set of conditions or criteria & we can use this function to sum based on multiple criteria. We don’t need to type 2 different functions to sum in the function bar.

Here, the syntax of this function is.

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

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

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

📌 Steps:

  • Firstly, in the cell L10, type:
=SUMIFS(D5:D14, B5:B14, L8, C5:C14,L9)
  • Press ENTER & you’ll get the return value.

Read More: SUMIF with Multiple Criteria in Different Columns in Excel


ii) Use of  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 the 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.

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


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

Now, we can input date criteria in our functions. Here, 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. In addition, we want to know the total payments that the shop will receive after delivering the rest of the products (purchased after May) from the date 08-July-2021.

📌 Steps:

  • In cell I16, we have to type:
=SUMIFS(F5:F27,D5:D27,">=6/1/2021",E5:E27,">="&I14)
  • Press ENTER & you’ll find the total payments as $5,990 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 the SUMIFS function. In our dataset now, there are a few missing values in the columns of Date of Purchase & Date of Delivery. At this time, we want to know the total price of the products that have been purchased but not delivered yet.

📌 Steps:

  • Firstly, in cell I16, our formula for the given criteria will be:
=SUMIFS(F5:F27,D5:D27,"<>",E5:E27,"=")
  • Next, press ENTER & you’ll find a total of $3320 to be received after deliveries of the rest of the products or devices.

Here, we’re using "<>" to exclude the blank cells & insert "=" as another criterion that denotes the inclusion of blank cells.

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

Read More: SUMIF and VLOOKUP Functions Across Multiple Sheets in Excel


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

In this section, we’ll determine the total number of sales in June for HP, Lenovo & Asus devices. We will do it by using array formula.

📌 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. Actually, 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 the SUMPRODUCT along with SUMIF functions to obtain a similar result.

📌 Steps:

  • In cell L10, our formula will be:
=SUMPRODUCT(SUMIF(B5:B14,L7:L9,I5:I14))
  • Then, press ENTER & you’re done.


Practice Section

Now, you can practice the explained method by yourself.


Download Practice Workbook

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


Concluding Words

We hope all these methods mentioned in the article will help you to use these in your regular Excel tasks. Also, you can suggest in the comment section if you think we’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 our website.


Related Articles


<< Go Back to SUMIF Multiple Criteria | Excel SUMIF Function | Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Nehad Ulfat
Nehad Ulfat

NEHAD ULFAT is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET but switched gears, working as a content developer.  In this role, he creates techy content all about Excel... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo