Sumif multiple columns

Sumif multiple columns DEFAULT

How to use Excel SUMIFS and SUMIF with multiple criteria - formula examples

This tutorial explains the difference between the SUMIF and SUMIFS functions in terms of their syntax and usage, and provides a number of formula examples to sum values with multiple AND / OR criteria in Excel 2019, 2016, 2013, 2010, 2007, 2003 and lower.

As everyone knows, Microsoft Excel provides an array of functions to perform various calculations with data. A few articles ago, we explored COUNTIF and COUNTIFS, which are designed for counting cells based on a single condition and several conditions, respectively. Last week we covered Excel SUMIF that adds values meeting the specified criteria. Now it's time to go over the plural version of SUMIF - Excel SUMIFS that allows summing values by multiple criteria.

Those who are familiar with the SUMIF function might think that converting it to SUMIFS takes just an extra "S" and a few additional criteria. This would seem quite logical… but "logical" it's not always the case when dealing with Microsoft : )

Excel SUMIF function - syntax & usage

The SUMIF function is used to conditionally sum values based on a single criteria. We discussed the SUMIF syntax in detail in the previous article, so let me give you just a quick summary now.

SUMIF(range, criteria, [sum_range])
  • - the range of cells to be evaluated by your criteria, required.
  • - the condition that must be met, required.
  • - the cells to sum if the condition is met, optional.

As you see, the syntax of the Excel SUMIF function allows for one condition only. And still, we say that Excel SUMIF can be used to sum values with multiple criteria. How can that be? By adding the results of several SUMIF functions and by using SUMIF formulas with array criteria, as demonstrated in the examples that follow.

Excel SUMIFS function - syntax & usage

You use SUMIFS in Excel to find a conditional sum of values based on multiple criteria. The SUMIFS function was introduced in Excel 2007, so you can use it in all modern versions of Excel 2019, 2016, 2013, 2010 and 2007.

Compared to SUMIF, the SUMIFS syntax is a little bit more complex:

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

The first 3 arguments are mandatory, additional ranges and their associated criteria are optional.

  • - one or more cells to sum, required. This can be a single cell, a range of cells or a named range. Only cells with numbers are summed; blank and text values are ignored.
  • - the first range to be evaluated by the associated criteria, required.
  • - the first condition that must be met, required. You can supply the criteria in the form of a number, logical expression, cell reference, text or another Excel function. For example you can use criteria such as 10, ">=10", A1, "cherries" or TODAY().
  • - these are additional ranges and criteria associated with them, optional. You can use up to 127 range/criteria pairs in SUMIFS formulas.
Important! The SUMIFS function works with AND logic, meaning that a cell in the sum range is summed only if it meets all of the specified criteria (all the criteria are true for that cell).

And now, let's have a look at the Excel SUMIFS formula with two conditions. Suppose, you have a table listing the consignments of fruit from different suppliers. You have the fruit names in column A, suppliers' names in column B, and quantity in column C. What you want is to find out a sum of amounts relating to a given fruit and supplier, e.g. all apples supplied by Pete.
Data to be summed with two conditions

When you're learning something new, it's always a good idea to start with simple things. So, to begin with, let's define all the arguments for our SUMIFS formula:

  • sum_range - C2:C9
  • criteria_range1 - A2:A9
  • criteria1 - "apples"
  • criteria_range2 - B2:B9
  • criteria2 - "Pete"

Now assemble the above parameters, and you will get the following SUMIFS formula:


An example of the Excel SUMIFS formula with two conditions

To refine the formula further, you can replace the text criteria "apples" and "Pete" with cell references. In this case, you won't have to change the formula to calculate the quantity of other fruit from a different supplier:

Using SUMIFS and SUMIF in Excel - things to remember

Since the aim of this tutorial is to cover all possible ways to sum values by several conditions, we will discuss formula examples with both functions - Excel SUMIFS and SUMIF with multiple criteria. To use them correctly, you need to clearly understand what these two functions have in common and in what way they are different.

While the common part is clear - similar destination and parameters - the differences are not so obvious, though very essential.

1. The order of arguments

In Excel SUMIF and SUMIFS functions, the order of arguments is different. In particular, sum_range is the 1st parameter in SUMIFS, but it is 3rd in SUMIF.

At first sight, it may seem that Microsoft deliberately complicates the learning curve for its users. However, upon a closer look, you will see the reasoning behind it. The point is that sum_range is optional in SUMIF. If you omit it, no problem, your SUMIF formula will sum values in the range (first parameter).

In SUMIFS, the sum_range is very important and obligatory, and that is why it comes first. Probably Microsoft guys thought that after adding the 10th or 100th range / criteria pair, someone might forget to specify the range to sum : )

2. Sum range and criteria range should be equally sized

In the SUMIF function, the sum_range argument does not necessarily have to be of the same size as the range argument, as long as you have the top left cell right. In Excel SUMIFS, each criteria_range must contain the same number of rows and columns as the sum_range parameter.

For example, will return the correct result because Excel considers only the upper leftmost cell in the sum_range argument (C2 in this example, which is correct), and then includes as many columns and rows as contained in the range argument.

The SUMIFS formula will return the #VALUE! error because criteria_range2 (B2:B10) does not match in size criteria_range1 (A2:A9) and sum_range (C2:C9).

Alright, enough strategy (i.e. theory), let's get into the tactics (i.e. formula examples : )

How to use SUMIFS in Excel - formula examples

A moment ago, we discussed a simple SUMIFS formula with two text criteria. In the same manner, you can use Excel SUMIFS with multiple criteria expressed by numbers, dates, logical expressions, and other Excel functions.

Example 1. Excel SUMIFS with comparison operators

In our fruit suppliers table, suppose, you want to sum all deliveries by Mike with Qty. 200 or more. To do this, you use the comparison operator "greater than or equal to" (>=) in criteria2 and get the following SUMIFS formula:


Excel SUMIFS formula with comparison operators

Note. Please pay attention that in Excel SUMIFS formulas, logical expressions with comparison operators should always be enclosed in double quotes ("").

We covered all possible comparison operators in detail when discussing Excel SUMIF function, the same operators can be used in SUMIFS criteria. For example, the following formula with return the sum of all values in cells C2:C9 that are greater than or equal to 200 and less than or equal to 300.

Example 2. Using Excel SUMIFS with dates

In case you want to sum values with multiple criteria based on the current date, use the TODAY() function in your SUMIFS criteria, as demonstrated below. The following formula sums values in column D if a corresponding date in column C falls within the last 7 days, including today:


Excel SUMIFS formula for dates

Note. When you use another Excel function together with a logical operator in the criteria, you have to use the ampersand (&) to concatenate a string, for example "<="&TODAY().

In a similar fashion, you can use the Excel SUMIF function to sum values in a given date range. For example, the following SUMIFS formula adds the values in cells C2:C9 if a date in column B falls between 1-Oct-2014 and 31-Oct-2014, inclusive.

The same result can be achieved by calculating the difference of two SUMIF functions, as demonstrated in this example - How to use SUMIF to sum values in a given date range. However, Excel SUMIFS is much easier and more understandable, isn't it?

Example 3. Excel SUMIFS with blank and non-blank cells

When analyzing reports and other data, you may often need to sum values corresponding either to empty or non-empty cells.

CriteriaDescriptionFormula Example
Blank cells"="Sum values corresponding to blank cells that contain absolutely nothing - no formula, no zero length string.=SUMIFS(C2:C10, A2:A10, "=", B2:B10, "=")

Sum values in cells C2:C10 if the corresponding cells in columns A and B are absolutely empty.

""Sum values corresponding to "visually" blank cells including those that contain empty strings returned by some other Excel function (for example, cells with a formula like ="").=SUMIFS(C2:C10, A2:A10, "", B2:B10, "")

Sum values in cells C2:C10 with the same conditions as the above formula, but includes empty strings.

Non-blank cells"<>"Sum values corresponding to non-empty cells, including zero length strings.=SUMIFS(C2:C10, A2:A10, "<>", B2:B10, "<>")

Sum values in cells C2:C10 if the corresponding cells in columns A and B are not empty, including cells with empty strings.

SUM-SUMIF
or
SUM / LEN
Sum values corresponding to non-empty cells, not including zero length strings.=SUM(C2:C10) - SUMIFS(C2:C10, A2:A10, "", B2:B10, "")

=SUM((C2:C10) * (LEN(A2:A10)>0)*(LEN(B2:B10)>0))

Sum values in cells C2:C10 if the corresponding cells in columns A and B are not empty, cells with zero length strings are not included.

And now, let's see how you can use a SUMIFS formula with "blank" and "non-blank" criteria on real data.

Suppose, you have an order date in column B, delivery date in column C and Qty. in column D. How do you find the total of products that have not been delivered yet? That is, you want to know the sum of values corresponding to non-empty cells in column B and empty cells in column C.

The solution is to use the SUMIFS formula with 2 criteria:


Excel SUMIFS formula for blank and non-blank cells

Using Excel SUMIF with multiple OR criteria

As noted in the beginning of this tutorial, the SUMIFS function is designed with AND logic. But what if you need to sum values with multiple OR criteria, i.e. when at least one of the conditions is met?

Example 1. SUMIF + SUMIF

The simplest solution is to sum the results returned by several SUMIF functions. For example, the following formula demonstrates how to find the total of products delivered by Mike and John:


Excel SUMIF formula with multiple OR criteria

As you see, the first SUMIF function adds the quantities corresponding to "Mike", the other SUMIF function returns the amounts relating to "John" and then you add these 2 numbers.

Example 2. SUM & SUMIF with an array argument

The above solution is very simple and may get the job done quickly when there are only a couple of criteria. But a SUMIF + SUMIF formula may grow up enormously if you want to sum values with multiple OR conditions. In this case, a better approach is using an array criteria argument in the SUMIF function. Let's examine this approach now.

You can start by listing all of your conditions separated by commas and then enclose the resulting comma-separated list in {curly brackets}, which is technically called an array.

In the previous example, if you want to sum the products delivered by John, Mike and Pete, your array criteria will look like {"John","Mike","Pete"}. And the complete SUMIF function is .

The array argument consisting of 3 values forces your SUMIF formula to return three separate results, but since we write the formula in a single cell, it would return the first result only - i.e. the total of products delivered by John. To get this array-criteria approach to work, you have to use one more little trick - enclose your SUMIF formula in a SUM function, like this:


Using SUM & SUMIF with an array argument to sum values with multiple OR criteria

As you see, an array criteria makes the formula much more compact compared to SUMIF + SUMIF, and lets you add as many values as you like in the array.

This approach works with numbers as well as with text values. For instance, if instead of the suppliers' names in column C, you had supplier IDs like 1, 2, 3 etc., then your SUMIF formula would look similar to this:

Unlike text values, numbers needn't be enclosed in double quotes in array arguments.

Example 3. SUMPRODUCT & SUMIF

In case, your preferred way is to list the criteria in some cells rather that specify them directly in the formula, you can use SUMIF in conjunction with the SUMPRODUCT function that multiplies components in the given arrays, and returns the sum of those products.

Where G2:G4 are the cells containing your criteria, the suppliers' names in our case, as illustrated in the screenshot below.

But of course, nothing prevents you from listing the values in an array criteria of your SUMIF function if you want to:

The result returned by both formulas will be identical to what you see in the screenshot:
Sum with multiple criteria using the SUMPRODUCT / SUMIF formula

Excel SUMIFS with multiple OR criteria

If you want to conditionally sum values in Excel not simply with multiple OR conditions, but with several sets of conditions, you will have to use SUMIFS instead of SUMIF. The formulas will be very similar to what we've just discussed.

As usual, an example might help to illustrate the point better. In our table of fruit suppliers, let's add the Delivery Date (column E) and find the total quantity delivered by Mike, John and Pete in October.

Example 1. SUMIFS + SUMIFS

The formula produced by this approach includes a lot of repetition and looks cumbersome, but it is easy to understand and, most importantly, it works : )

As you see, you write a separate SUMIFS function for each of the suppliers and include two conditions - equal to or greater than Oct-1 (">=10/1/2014",) and less than or equal to Oct 31 ("<=10/31/2014"), and then you sum the results.
Add several SUMIFS functions to sum values with multiple OR conditions

Example 2. SUM & SUMIFS with an array argument

I've tried to explain the essence of this approach in the SUMIF example, so now we can simply copy that formula, change the order of arguments (as you remember it is different in SUMIF and SUMIFS) and add additional criteria. The resulting formula is more compact than SUMIFS + SUMIFS:

The result returned by this formula is exactly the same as you see in the screenshot above.

Example 3. SUMPRODUCT & SUMIFS

As you remember, the SUMPRODUCT approach differs from the previous two in the way that you enter each of your criteria in a separate cell rather that specify them directly in the formula. In case of several criteria sets, the SUMPRODUCT function won't suffice and you will have to employ ISNUMBER and MATCH as well.

So, assuming that the Supplies Names are in cells H1:H3, Start Date is in cell H4 and End Date in cell H5, our SUMPRODUCT formula takes the following shape:


The SUMPRODUCT formula to sum values with multiple OR conditions

Many people wonder why use double dash (--) in SUMPRODUCT formulas. The point is that Excel SUMPRODUCT ignores all but numeric values, while the comparison operators in our formula return Boolean values (TRUE / FALSE), which are non-numeric. To convert these Boolean values to 1's and 0's, you use the double minus sign, which is technically called the double unary operator. The first unary coerces TRUE/FALSE to -1/0, respectively. The second unary negates the values, i.e. reverses the sign, turning them into +1 and 0, which the SUMPRODUCT function can understand.

I hope the above explanation makes sense. And even if it doesn't, just remember this rule of thumb - use the double unary operator (--) when you are using comparison operators in your SUMPRODUCT formulas.

Using Excel SUM in array formulas

As you remember, Microsoft implemented the SUMIFS function in Excel 2007. If someone still uses Excel 2003, 2000 or earlier, you will have to use a SUM array formula to add values with multiple AND criteria. Naturally, this approach works in modern versions of Excel 2013 - 2007 too, and can be deemed an old-fashioned counterpart of the SUMIFS function.

In the SUMIF formulas discussed above, you have already used array arguments, but an array formula is something different.

Example 1. Sum with multiple AND criteria in Excel 2003 and earlier

Let's get back to the very first example where we found out a sum of amounts relating to a given fruit and supplier:
Source table to sum values by several conditions

As you already know, this task is easily accomplished using an ordinary SUMIFS formula:

And now, let's see how the same task can be fulfilled in early "SUMIFS-free" versions of Excel. First off, you write down all the conditions that should be met in the form of range="condition". In this example, we have two range/condition pairs:

Condition 1: A2:A9="apples"

Condition 2: B2:B9="Pete"

Then, you write a SUM formulas that "multiplies" all of your range/condition pairs, each enclosed in brackets. The last multiplier is the sum range, C2:C9 in our case:

As illustrated in the screenshot below, the formula perfectly works in the latest Excel 2013 version.
An example of using Excel SUM in an array formula

Note. When entering any array formula, you must press Ctrl + Shift + Enter. Once you do this, your formula gets enclosed in {curly braces}, which is a visual indication that an array formula is entered correctly. If you try typing the braces manually, your formula will be converted to a text string, and it won't work.

Example 2. SUM array formulas in modern Excel versions

Even in modern versions of Excel, the power of the SUM function should not be underestimated. The SUM array formula is not simply gymnastics of the mind, but has a practical value, as demonstrated in the following example.

Suppose, you have two columns, B and C, and you need to count how many times column C is greater than column B, when a value in column C is greater or equal to 10. An immediate solution that comes to mind is using the SUM array formula:


Using SUM array formulas in modern Excel versions

Don't see any practical application to the above formula? Think about it in another way : )

Suppose, you have the orders list like shown in the screenshot below and you want to know how many products have not been delivered in full by a given date. Translated into Excel's language, we have the following conditions:

Condition 1: A value in column B (Ordered items) is greater than 0

Condition 2: A value in column C (Delivered) in less than in column B

Condition 3: A date in column D (Due date) is less than 11/1/2014.

Putting the three range/condition pairs together, you get the following formula:


An example of SUM array formula in Excel 2013

Well, the formula examples discussed in this tutorial have only scratched the surface of what Excel SUMIFS and SUMIF functions can really do. But hopefully, they have helped pointing you in the right direction and now you can sum values in your Excel workbooks no matter how many intricate conditions you have to consider.

You may also be interested in

Sours: https://www.ablebits.com/office-addins-blog/2014/11/12/excel-sumifs-sumif-multiple-criteria/

How to Sum if One Criteria Multiple Columns

We can sum multiple columnsconditionally by specifying a criterion. We can do this with a formula that is based on the SUMPRODUCT function. The steps below will walk through the process.

Figure 1: Result of the Sum of Multiple Columns with Football as the Criterion

Basic Formula

Formula

Setting up the Data

  • We will set up the data by inserting the values into Column A to Column D
  • We will input football sum in Cell E4 as football will be the criterion in this example
  • The result will be displayed in Cell F4 which is currently empty

Figure 2: How to Sum if One Criteria Multiple Columns

Inserting the Formula

  • We will click on Cell F4
  • We will insert the formula below into the cell

Figure 3: How to Sum if One Criteria Multiple Columns

Figure 4: Result of the Sum of Multiple Columns with Football as the Criterion

Explanation

Formula:

In the formula, we use the SUMPRODUCT function to check if Cell A4 to Cell A9 contains “football.” We will have an array result of TRUE or FALSE like this:

This is multiplied by the values in the range B4:D9, where TRUE represents 1 and FALSE represents 0.

The result inside the SUMPRODUCT function looks like this:

The product is summed and the result is 24 as shown in figure 4.

Instant Connection to an Expert through our Excelchat Service

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Sours: https://www.got-it.ai/solutions/excel-chat/excel-tutorial/sum/sum-if-one-criteria-multiple-columns
  1. Craftsman 30 ft tape measure
  2. Anki app reddit
  3. Mickey mouse valentine clipart
  4. Tvs h1288x
  5. Plastic drawers storage

SUMIF with Multiple Columns – The SUMIFS Function in Excel

This SUMIFS Excel Function tutorial is suitable for users Excel 2013,2016, 2019 and Excel for Microsoft 365.

OBJECTIVE

Use the SUMIFS function in Excel to add numbers in a range of cells based on single or multiple criteria.

SUMIFS Excel Function Made Easy

The SUMIFS Excel function is a much welcome enhancement to an old Excel favourite, SUMIF. Part of the Maths/Trig group of formulas, it can be used to add a range of numbers based on one or more pieces of criteria, or in simpler terms, SUMIFS works on multiple columns.

Related: 

Compare Two Lists Using VLOOKUP

Creating an Excel Dashboard in 5 Minutes

Using GETPIVOTDATA in Excel

This makes it different from the Excel SUMIF function, which could only handle one piece of criteria.

Let’s take a step by step approach to understanding this mildly complex SUMIFs Excel function. Let’s break down this explanation with the help of these steps. 

  1. SUMIFS Excel Syntax
  2. Video Tutorial – SUMIFS Function in Excel
  3. Back To Basics – SUMIF Function in Excel
  4. Using Excel SUMIFS Function To Sum Values With Multiple Criteria
  5. SUMIFS Multiple Criteria Examples

Let’s take a look at them in detail.

SUMIFS Excel Syntax

One major difference between SUMIF and SUMIFS in terms of syntax is that when using Excel SUMIFS, the sum_range argument is specified first.

Once you have specified the range to be added, you can then specify the criteria range followed by the criteria. You can include up to 127 pairs of criteria.

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

Video Tutorial – SUMIFS Function in Excel

BACK TO BASICS – SUMIF function in Excel

Before we tackle SUMIFS function with multiple columns, let’s remind ourselves how Excel SUMIF function works.

Excel SUMIF can only handle one piece of criteria.

In the example below, I want to sum the Price for all Skirts. The Price is the sum_range, and the Skirt is the criteria.

SumIf example

Note that the sum_range is entered last.

Sum_Range is entered last in the SUMIF function

The range argument is the range of cells where I want to look for the criteria, A2:A19. The criteria argument is the criteria F2. The sum_range is the range I want to sum, D2:D19.

Note: I have my criteria set up as data validation drop-down lists to select different items, colors, and sizes. This also means I can use a cell reference as my criteria (F2). Alternatively, I could replace F2 in the formula with the name of my criteria. Additionally, I must enclose text entries in quote marks.

SUMIF Result

This is the result. As the list is reasonably short, I can do a visual check to ensure the calculation has worked correctly.

Also Read: 

The Excel FILTER Function

Ten Ways to Clean Data in Excel

Basic Forecasting in Excel: Recorded Webinar

Use SUMIF for single criterion

So that is how you sum values based on one piece of criteria.

Using Excel SUMIFS Function To Sum Values With Multiple Criteria

You have to use the SUMIFS function in Excel to sum values with multiple criteria, as the SUMIF function can handle only one criterion at a time. That is you cannot use SUMIF with multiple column

ns at a time. On the other hand, the SUMIFS formula in Excel can work with multiple criteria simultaneously.

Now, I’ll show you how to use the SUMIFS function in Excel to calculate the sum based on multiple criteria.

SUMIFS Multiple Criteria Examples

SUMIFS – 2 Criteria

In the first example, I am going to sum using two pieces of criteria. I want to find out the total Price for all White Shirts.

When using SUMIFS Excel Function, the sum_range is specified first, D2:D19. The criteria_range1 is specified. Next, A2:A19 followed by criteria1, “Shirt.” Then, criteria_range2 is specified, B2:B19 followed by criteria2, “White.”

SUMIFS function in Excel

I could also construct this formula using cell references instead of text for my criteria.

SUMIFS Function in Excel - Cell References

SUMIFS – 3 Criteria

In this example, I am going to sum using three pieces of criteria.

I want to find out the total Price for all Blouses that are Blue and a size L.

SUMIFS function in Excel for 3 Criteria

This formula could also be written like this.

SUMIFS function in Excel - Using direct references

When using the SUMIFS function in Excel, the sum_range is specified first, D2:D19. The criteria_range1, A2:A19 is specified next. Followed by criteria 1, “Shirt.” Then, criteria_range2 is specified, B2:B19 followed by criteria 2, “White.”

SUMIFS – Using Logical Operations

In this example, I have added a ‘Revenue’ and a ‘Profit’ column.

This time, I want to use Excel SUMIFS to sum the total Revenue for all Blouses where the Profit is greater than $2.00. To accomplish this, I can use a logical operator as my criteria.

First, specify the sum_range, D2:D19. Next, specify criteria_range1, A2:A19 and the criteria1, G2. Then, specify criteria_range2 and the criteria2. Criteria2 is a logical test enclosed in quote marks.

I am only interested in summing the Revenue when the Profit is greater than (>) $2.00.

SUMIFS function with logical operator

SUMIFS – Using 5 Criteria

In this example, I have summed the Revenue for all Blue Blouses, size L, and with a Profit of greater than $1.40.

The answer is $23.00 as only one item matches all criteria.

SUMIFS function using logical operator for 5 Criteria

These are just a few simple examples of how to use SUMIFS in Excel to perform calculations with multiple criteria.

FAQs

Can we add multiple ranges of data using SUMIFS in Excel? 

Yes we can add multiple ranges of data using SUMIFs by adding together the results of two SUMIFS functions. 

What is the difference between SUMIF and SUMIFS?

SUMIF is used to add data based on a single criterion, while SUMIFS can be used to add data based on multiple criteria. 

How many arguments does the Sumifs function have in Excel?

The SUMIFS Excel function can handle upto 127 pairs of Criteria Range & Criteria Arguments. 

Suggested Reads: 

Compare Two Lists Using VLOOKUP

XLOOKUP Google Sheets – The Alternatives!

Excel vs Google Sheets

SUMIFs & other complex Excel functions made easy….

What do you think about the SUMIFs excel function? Isn’t it one of the smartest Excel functions out there? 

It is the easiest way to add rows of data based on a given condition. 

If you have any doubts regarding this or any other interesting suggestions for SUMIFs function’s application, please let us know in the comment section below. We’re always glad to help you.  

If you are hungry for more rich quality Excel educational content check out our other articles on this blog. 

For more, free Excel training from Simon Sez IT. Take a look at our YouTube Channel or Resource Centre.

If you are ready to properly learn Excel, then take a look at the Simon Sez IT Excel Course Library.

Deborah Ashby

Deborah Ashby is a TAP Accredited IT Trainer, specializing in the design, delivery, and facilitation of Microsoft courses both online and in the classroom. She has over 11 years of IT Training Experience and 24 years in the IT Industry. To date, she's trained over 10,000 people in the UK and overseas at companies such as HMRC, the Metropolitan Police, Parliament, SKY, Microsoft, Kew Gardens, Norton Rose Fulbright LLP. She's a qualified MOS Master for 2010, 2013, and 2016 editions of Microsoft Office and is COLF and TAP Accredited and a member of The British Learning Institute.

Sours: https://www.simonsezit.com/article/how-to-use-the-sumifs-function-in-excel/
How to do SUMIF with Multiple Columns Tutorial - SUMIFS in Excel

SUMIF over Multiple Columns

  • 05-09-2017, 01:48 AM#1

    Musiclover119 is offline
    Registered User

    SUMIF over Multiple Columns

    Dear All,

    Does anyone know of how to do a SUMIF over multiple columns? With the caveat that range and sum area are not right next to each other. For example, like:

    =SUMIF('Raw Data'!M$3:$Z$225,' Automation Calculations'!AH$6,'Raw Data'!AH3:AQ225)

    Regards,

    Christoph

  • 05-09-2017, 01:56 AM#2


  • 05-09-2017, 02:00 AM#3

    Musiclover119 is offline
    Registered User

    Re: SUMIF over Multiple Columns

    Dear Ankur,

    Thank you,

    but I essentially just have one criteria area (the one in 'Raw Data'!M$3:$Z$225), but I want to sum all the cells in "'Raw Data'!AH3:AQ225", which correspond with Automation Calculations'!AH$6 in 'Raw Data'!M$3:$Z$225. This does not work as Excel just sums AH3:AH225.

    Regards,

    Christoph

  • 05-09-2017, 02:04 AM#4


  • 05-09-2017, 02:33 AM#5


  • 05-09-2017, 02:35 AM#6


  • 05-09-2017, 05:47 AM#7

    Musiclover119 is offline
    Registered User

    Re: SUMIF over Multiple Columns


    Sorry for that, I was unaware of that. Full disclosure I cross-posted, but now deleted the post. You can find it under: http://stackoverflow.com/questions/4...59544_43862105

    Last edited by Musiclover119; 05-09-2017 at 06:03 AM.


  • 05-09-2017, 05:59 AM#8

    Musiclover119 is offline
    Registered User

    Re: SUMIF over Multiple Columns

    I looked at it, but my header are different.



    QuoteOriginally Posted by shukla.ankur281190View Post

    Ok Try

    =SUMPRODUCT(('Raw Data'!M$3:$Z$225=' Automation Calculations'!AH$6)*('Raw Data'!AH3:AQ225))

    Are criteria ranges in multiple columns???

    If above formula is not working

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

    I tried that, but it shows me "N/A". I attached the spreadsheet now. You can find the calculation in the second sheet. I highlighted it yellow.

    Last edited by Musiclover119; 05-09-2017 at 06:02 AM.


  • 05-09-2017, 06:13 AM#9


  • 05-09-2017, 06:33 PM#10

    Musiclover119 is offline
    Registered User

    Re: SUMIF over Multiple Columns

    Thank you Shukla,

    It is not quit what I have been looking for. My intent is to divide the individual value of B20 (in the case now 3127) by all the values of Ra. So that I can see what percentage Individual focus is from all the activities.

    I know I could just take the sum from above, but I want to automate the whole process.


  • 05-10-2017, 12:53 AM#11


  • 05-10-2017, 02:10 AM#12

    Musiclover119 is offline
    Registered User

    Re: SUMIF over Multiple Columns

    Awesome! Solved. Thank you so much

  • 05-10-2017, 02:14 AM#13


  • 04-07-2020, 01:04 AM#14

    Pratiklangi1988 is offline
    Registered User

    Re: SUMIF over Multiple Columns

    Hi.
    I am trying to use SUMIFS function to get output from multiple rows & columns. But it is showing #Value.
    Can anyone please help on the same. I have attached the excel


  • 04-07-2020, 02:35 AM#15


  • Sours: https://www.excelforum.com/

    Multiple columns sumif

    How to sum multiple columns based on single criteria in Excel?

    In Excel, you may always need to sum multiple columns based on one criteria. For example, I have a range of data as following screenshot shown, now, I want to get the total values of KTE in three months - Jan, Feb and Mar.

    Sum multiple columns based on single criteria with a helper column

    Sum multiple columns based on single criteria with an array formula

    Sum multiple columns based on single criteria with an awesome feature


    Sum multiple columns based on single criteria with a helper column

    In Excel, we can create a helper column to sum the total values for each row, and then use the sumif function to sum this column based on criteria, please do as this:

    1. In this example, you can sum the total values for each row first, please type this formula: =sum(B2:D2), then drag the fill handle down to the cells that you want to apply this formula, and the total values of each row will be displayedsee screenshot:

    2. And next, you can apply the below sumif function to sum the data in helper column E based on the criteria:

    =SUMIF(A2:A10, G2, E2:E10)

    Then, press Enter key on the keyboard, and you will get the total number based on the specific criteria. See screenshot:

    Tips: In the above formula:

    • A2:A10 refers to the range of cells that you want to apply the criteria against;
    • G2 is the criterion that the items are to be added;
    • E2:E10 specifies the cells to be added.

    lookup and sum all the matching values in rows or columns

    Kutools for Excel's LOOKUP and Sum feature helps you to lookup the specific value and get the summation of all the matching values in rows or columns as you need. Click to download Kutools for Excel!

    Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days. Download and free trial Now!


    Sum multiple columns based on single criteria with an array formula

    If you don’t like to use the helper column to solve this problem, here also is an array formula that can do you a favor.

    1. Enter or copy the following formula in your specific cell - G2:

    =SUM((B2:B10+C2:C10+D2:D10)*(--(A2:A10=F2)))

    2. And then press the Ctrl + Shift +Enter keys together, and you will get the correct result.

    Tips: In the above formula:

    • B2:B10, C2:C10 and D2:D10, indicate the columns that you want to sum, if you have more columns data need to sum, just add the column range as your need;
    • A2:A10 refers to the range of cells that you want to apply the criteria against;
    • F2 is the criterion that the items are to be added.

    Sum multiple columns based on single criteria with an awesome feature

    to deal with this task as quickly as possible, Kutools for Excel's LOOKUP and Sum feature also can help you.

    Tips:To apply this LOOKUP from Bottom to Top feature, firstly, you should download the Kutools for Excel, and then apply the feature quickly and easily.

    After installing Kutools for Excel, please do as this:

    1. Click Kutools > Super LOOKUP > LOOKUP and Sum, see screenshot:

    2. In the LOOKUP and Sum dialog box, please do the following operations:

    • Select Lookup and sum matched value(s) in row(s) option under the Lookup and Sum Type section;
    • Specify the lookup value, output range and the data range that you want to use;
    • Select Return the sum of all matched values option from the Options.

    3. Then, click OK button, and the total value of all the matched records has been calculated at once, see screenshot:

    Download and free trial Kutools for Excel Now!


    More relative articles:

    • Sumif with multiple criteria in one column
    • In Excel, SUMIF function is a useful function for us to sum cells with multiple criteria in different columns, but with this function, we can also sum cells based on multiple criteria in one column. In this article. I will talk about how to sum values with more than one criteria in same column.
    • Sumif with one or more criteria in Excel
    • In Excel, sum values based on one or more criteria is a common task for most of us, the SUMIF function can help us to quickly sum the values based on one condition and the SUMIFS function help us to sum values with multiple criteria. This article, I will describe how to sum with one or more criteria in Excel?
    • Sumif Cell Values Between Two Given Dates In Google Sheets
    • In my Google sheet, I have two columns which contain a date column and order column, now, I want to sum the order column cells based on the date column. For example, sum values between 2018/5/15 and 2018/5/22 as following screenshot shown. How could you solve this job in Google sheets?
    • Sum Cells When Value Changes In Another Column
    • When you work on Excel worksheet, sometime, you may need to sum cells based on group of data in another column. For example, here, I want to sum the orders in column B when the data changes in column A to get the following result. How could you solve this problem in Excel?
    • Vlookup Across Multiple Sheets And Sum Results In Excel
    • Supposing, I have four worksheets which have the same formatting, and now, I want to find the TV set in the Product column of each sheet, and get the total number of order across those sheets as following screenshot shown. How could I solve this problem with an easy and quick method in Excel?

    The Best Office Productivity Tools

    Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

    • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
    • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
    • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
    • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
    • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
    • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
    • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
    • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
    • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
    kte tab 201905

    Read More...Free Download...Purchase... 


    Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

    • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
    • Open and create multiple documents in new tabs of the same window, rather than in new windows.
    • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
    officetab bottom
    Sours: https://www.extendoffice.com/documents/excel/2466-excel-sum-multiple-columns-based-on-single-criteria.html
    Excel SUMIFS Sum With Multiple Criteria

    How to Sum Multiple Columns with Condition

    Fb-Button

    We know how to sum values from one column on multiple condition. We use SUMIFS function for that. But how do we sum multiple columns on one condition. This article is all about summing values from multiple columns on condition.
    0013
    We can sum multiple columns on one condition without using SUMIF function. Here we will use SUMPRODUCT function of excel.
    Generic Formula

    =SUMPRODUCT((criteria_range=criteria)*(sum_range))

    Criteria_range: This is the range in which criteria will be matched.
    Criteria: this is the criteria or condition.
    Sum_range: the sum range. This can have multiple columns but same rows as criteria range.

    Let’s see it in action.
    Example: Sum the money spent on mango.

    In above image, we have this table of amount spent on different fruits in different months. We just need to get the total amount spent on mangoes in all these months.

    In I2 the formula is

    =SUMPRODUCT((B2:B9=H2)*C2:E9)

    This returns 525 as total amount spent on mangos. You can see this in image above.
    How it works?
    Well, it is easy. Let’s break down the formula and understand it in peaces.
    (B2:B9=H2): This part compares each value in range B2:B9 with H2 and returns an array of TRUE and FALSE. {FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE}.

    (B2:B9=H2)*C2:E9: Here we multiplied each value in above array with values in C2:E9. C2:C9 are also treated as an 2D array. Finally this statement returns a 2D array of {0,0,0;47,57,67;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;108,118,128}.
    Now SUMPRODUCT looks like this:
    SUMPRODUCT({0,0,0;47,57,67;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0;108,118,128}). It has values of mango only. It sums them up and returns the result as 525.

    Another method can be having a totals column and then use it with SUMIF function to get the sum of all columns. But that’s not what we want to do.

    So yeah guys, this how you can sum multiple columns with condition without using sumif function. Let me know if you have any doubts regarding this article or any other article on this site. You can also ask queries regarding Excel 2019, 2016, 2013 and older.

    Related Article:
    How to Use SUMIF Function in Excel
    SUMIFS with dates in Excel
    SUMIF with non-blank cells
    How to Use SUMIFS Function in Excel
    SUMIFS using AND-OR logic

    Popular Articles:
    The VLOOKUP Function in Excel
    COUNTIF in Excel 2016
    How to Use SUMIF Function in Excel

    Sours: https://www.exceltip.com/summing/how-to-sum-multiple-columns-with-condition.html

    Now discussing:

    In certain circumstances, we have to calculate the sum value dealing with multiple columns. The SUMIFS function is a good choice in those cases.

    In this article, I’ll demonstrate five types of application of SUMIFS multiple columns in Excel. Also, I’ll show an effective alternative to the function.

    Download Practice Workbook

    Basics

    This section is mainly for beginners. Here, we’ll understand the SUMIFS function and the process of entering the formula in Excel. If you think, you’ve got these basics, move to the main section.

    What is SUMIFS Function?

    SUMIFS function is an Excel function that adds all values based on multiple criteria. This function was introduced in 2007. From its initiation, it’s becoming popular day by day.

    The syntax of the function is


    There are the following arguments in the function.

    sum_range – The range to be summed.

    range1 – The first range to evaluate.

    criteria1 – The criteria to use on range1.

    range2 – [optional] The second range to evaluate.

    How to Enter a Formula in Excel

    Do you know how can we insert a formula in Excel?

    Entering a formula in the Excel formula bar is quite a simple task.

    First, you have to select a blank cell where you want to show the output. Then input an equal sign (=). And then insert the formula with proper parenthesis. At last, press Enter. Furthermore, you can use the Fill Handle Tool (appears in the lower right of the selected cell as a small square) to copy the formula for other cells.

    SUMIFS Multiple Columns in Excel

    Let’s have a look at our dataset. Some popular Name of Sites is given along with the Number of Visits. Besides, the Platform which is used and also the Date of counting the number of visits are provided.

    Dataset of SUMIFS Multiple Columns

    Right now, we’ll do analysis based on SUMIFS multiple columns.

    Let’s get started.

    1. SUMIFS Multiple Columns with Single Criteria

    If you want to use single criteria e.g. the total number of visits to every site based on multiple columns and application of SUMIFS function, you may proceed with the following formula.


    Here, E5:E15 is the cell range for the number of visits, B5:B15 is for the name of sites, and G5 is the cell of a site namely exceldemy.com.

    SUMIFS Multiple Columns with Single Criteria

    2. SUMIFS Multiple Columns with Multiple Criteria

    What will happen if you want to use multiple criteria for multiple columns?

    As a case in our dataset, you can calculate the total number of visits based on the name of sites and the platform.

    In that case, the formula takes the following form


    Here, E5:E15 is the cell range for the number of visits, B5:B15 is for the name of sites, G5 is the cell of a site namely exceldemy.com, C5:C15 is for the platform, and H5 is a name of platform namely web.

    SUMIFS Multiple Columns with Multiple Criteria

    3. SUMIFS Multiple Columns Using OR Logic

    Let’s imagine another situation where you wish to calculate the total number of visits for every site based on a month like June.

    You may follow the formula as shown below. Don’t get afraid while watching the formula. Truly speaking, it’s easier to understand and actually the combination of three SUMIFS functions (for three sites).


    Remember, 6/1/2021 and 6/30/2021 for the whole month of June. Now, let’s understand the formula in part by part.

    In the First SUMIFS Syntax (for the site exceldemy.com),

    E5:E15 is the cell range for the number of visits, B5:B15 is for the name of sites, H6 is the name of the site, and D5:D15 is for the dates.

    In the Second SUMIFS Syntax (for the site softeko.digital),

    E5:E15 is the cell range for the number of visits, B5:B15 is for the name of sites, H7 is the name of the site, and D5:D15 is for the dates.

    In the Last SUMIFS Syntax (for the site udemy.com)

    E5:E15 is the cell range for the number of visits, B5:B15 is for the name of sites, H8 is the name of the site, and D5:D15 is for the dates.

    After entering the whole formula, you’ll get 18055 as the total number of visits.

    SUMIFS Multiple Columns Using OR Logic

    4. SUMIFS Multiple Columns with AND Logic

    Again, you may calculate the total considering specific criteria e.g. you want to count if the number of visits is greater than 2500 for every site.

    In that situation using SUMIFS function with AND logic, the formula will be


    In the formula, E5:E15 is the cell range for the number of visits, B5:B15 is for the name of sites, and >=2500 is for if the number of visits is greater than 2500.

    SUMIFS Multiple Columns with AND Logic

    5. SUMIFS Multiple Columns with Dates (Date Range)

    Also, you may find the sum for the number of visits using SUMIFS multiple columns for a specific date range. Like you need the total visits from 25-Jun-2021(start date) to 3-June-2021(end-date) for a particular site.

    So, the formula will be


    Here, E5:E15 is the cell range for the number of visits, B5:B15 is for the name of sites, H6 is the name of the site, D5:D15 is for the dates, H5 is the start date, and I5 is the end date.

    SUMIFS Multiple Columns with Dates

    Alternative of Using SUMIFS

    SUMPRODUCT Multiple Columns with Multiple Criteria

    SUMPRODUCT is an extraordinarily multifaceted, but rather flexible function that is suitable for summing such as SUMIFS.

    The syntax of SUMPRODUCT function is


    There are the following arguments in the function.

    array1 – The first array or range to multiply, then add.

    array2 – [optional] The second array or range to multiply, then add.

    In this article, I am showing the use of the SUMPRODUCT function in case of multiple criteria that we did similar to our second method.

    We are going to find the total number of visits based on the name of the sites and the platform.

    The formula will be


    Here, B5:B15 is for the name of sites, G5 is the cell of a site namely exceldemy.com, C5:C15 is for the platform, and H5 is a name of platform namely web, and E5:E15 is the cell range for the number of visits.

    SUMPRODUCT Multiple Columns with Multiple Criteria

    Things To Keep in Mind

    • Don’t forget to use the double quotes (e.g. input as “<“)
    • Input the formula with correct logic (e.g. don’t input “>” instead of “>=”)
    • Be careful about the file name, file location, and Excel extension name

    Conclusion

    This is how we can apply the SUMIFS function for multiple columns in different aspects. Also, I have shown an effective alternative to the function. Choose one according to your requirement.

    Thanks for visiting exceldemy.com, a valuable source of Excel problems-solutions.

    Sours: https://www.exceldemy.com/sumifs-multiple-columns/


    1921 1922 1923 1924 1925