How does sumifs function work in excel

In this example, the first parameter is A2:A6. This is the range of cells that will be tested to determine if they meet the criteria. In this example, the second parameter is D2. This is a reference to the cell D2 which contains the numeric value, The third parameter in the SUMIF function is the range of numbers that will potentially be added together.

In this example, the third parameter is C2:C6. A named range is a descriptive name for a collection of cells or range in a worksheet. If you are unsure of how to setup a named range in your spreadsheet, read our tutorial on Adding a Named Range. This would allow us to replace A2:A6 as the first parameter with the named range called years , as follows:. Question: I have a question about how to write the following formula in Excel.

I have a few cells, but I only need the sum of all the negative cells. There can be up to ranges. It is used to determine which cells to add. There can be up to criteria. Type of Function Worksheet function WS. The Name Manager window should now appear. Share on:. The above solution is very simple and may get the job done quickly when there are only a couple of criteria. Let's examine this approach now. 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.

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

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.

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 :. 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 second unary negates the values, i. I hope the above explanation makes sense.

Naturally, this approach works in modern versions of Excel - 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. Let's get back to the very first example where we found out a sum of amounts relating to a given fruit and supplier:.

The last multiplier is the sum range, C2:C9 in our case:.