SUMIF() and SUMIFS() Functions

To get the sum of values in a range of cells, we can use the SUMIF() function. The syntax is like this:

SUMIF(range, criteria, sum_range)

In this example we want to get the sum of sales from a region (“North”):

SUMIF1

As you can see on the formula bar, on cell E10 we have the following formula:

=SUMIF(B3:B8,”North”,E3:E8)

This will check on cells B3:B8 for the North region and will sum the values that correspond on cells E3:E8.

But if we wanted to get the sum of the North region for only a customer? Before Excel 2007, we needed to use the SUMPRODUCT() function. Now we have a new function in Excel that is called SUMIFS(). With this new function, we can have 2 or more conditions. Here’s the syntax:

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

The first argument is the range of cells to be summed. Then you should specify pairs of arguments that consist on the range to be check and the value to be match on that range. So, here’s an example of the sum of sales for the North region for customer “TMA, Inc”:

SUMIFS1

If you check the formula bar, our formula will sum the values on cells E3:E8 that have the region North on cells B3:B8 and the customer “TMA, Inc” on cells C3:C8.

When moving from SUMIF to SUMIFS, you should pay attention because the syntax is in a different order.

3 comentários:

Karen Solomon said...

I have a list with clients and a list of projects for each - when I go to my time sheet I can select my clients from a drop down list but I am unable to view the projects for the clients form indirect look up - only 1 works the rest nothing. It tells me ref error. I have redone data validation, created lists everything :-(

jppinto said...

Please take a look at this article to see if this is what you need: http://www.excel-user.com/2011/02/cascading-validation-lists.html

Petros said...

Do you want to read about the syntax and usage of an Excel 2013 or VBA function in your native language and practice with a demo workbook ?

The facts:
There are over 600 Excel & VBA functions in Office 2013.
Excel functions have been translated in 16 languages.
Microsoft offers over 20,000+ function help webpages in 50+ languages.

How to navigate fast among so many help pages ?

This free Ribbon Add-in will help you navigate to Microsoft's online help pages with embedded workbooks, which can be downloaded to your computer for function inspection and in-depth practice.

http://www.spreadsheet1.com/excel-2013-translated-functions-free-addins.html