Jump to content


Photo

How to use the SUMIFS function in Excel

formulas SUMIFS SUMIF

  • Please log in to reply
7 replies to this topic

#1 BillW1971

BillW1971

    Advanced Member

  • Members
  • PipPipPip
  • 35 posts
  • LocationLas Vegas

Posted 07 November 2013 - 01:18 AM

I did search and nothing comes up. How do I learn about this function? Thank you in advance.



#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 888 posts

Posted 07 November 2013 - 11:45 AM

The lesson you're looking for is Expert Skills Lesson 3-6: Use the SUMIF and COUNTIF logic functions to create conditional totals.

 

If you've read the lesson and need help with something specific, feel free to reply to this post with the details of what you're trying to do.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#3 BillW1971

BillW1971

    Advanced Member

  • Members
  • PipPipPip
  • 35 posts
  • LocationLas Vegas

Posted 07 November 2013 - 12:30 PM

Thank you taking the time to respond, but in 3-6 it covers the SUMIF and COUNTIF logic function. I am trying to learn the SUMIFS function. It is a different function, correct?



#4 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 888 posts

Posted 07 November 2013 - 01:08 PM

Hi Bill,

 

Yes, you're right - apologies for that.

 

The SUMIFS function isn't specifically covered in the course, but it's very similar to the SUMIF function.  The major difference is that SUMIFS allows you to specify multiple criteria, while SUMIF only allows a single criteria.

 

For example, let's say you want a SUMIFS function that sums values for a specific company and country.  Here's some example data:

sumifs1.png

 

To sum values for "Ace Byte Group" in the UK, you'd use the following SUMIFS formula:

=SUMIFS(C2:C21,A2:A21,"Ace Byte Group",B2:B21,"UK")

 

Of course, you could replicate this functionality using SUMIF, but you'd have to add an additional column containing an AND formula.  It's unfortunately not possible to use the AND function along with the SUMIF function.

 

The AND function is covered in Expert Skills Lesson 3-16: Use the AND and OR functions to construct complex Boolean criteria.

The SUMIF function is covered in Expert Skills Lesson 3-6: Use the SUMIF and COUNTIF logic functions to create conditional totals.

 

The AND formula would look something like:

=AND(A2="Ace Byte Group",B2="UK")

 

Then your SUMIF formula would look something like:

=SUMIF(D2:D21,"TRUE",C2:C21)

 

You can see all of these formulas in action in the attached example spreadsheet.

Attached File  SUMIFS.xlsx   9.95KB   268 downloads

sumifs2.png

 

I hope this information is useful, and as always feel free to reply if you need any more assistance.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#5 BillW1971

BillW1971

    Advanced Member

  • Members
  • PipPipPip
  • 35 posts
  • LocationLas Vegas

Posted 07 November 2013 - 11:57 PM

Thank you so much for explaining this, you rock



#6 BillW1971

BillW1971

    Advanced Member

  • Members
  • PipPipPip
  • 35 posts
  • LocationLas Vegas

Posted 22 February 2014 - 12:10 PM

Hello again excel,

I am trying to get this sumifs function but I am not sure I know exactly how it works and how to add to it. I was asking you question in another post and I don't believe I was asking the correct questions. My question is much easier now I look at this old post that you answered for me before. I just want to add to the sumifs function.

 

SUMIFS formula to get UK values for Ace Byte Group     101           SUMIF formula to get UK values for Ace Byte Group     101                 Can you use the simifs  formula for UK and USA for Ace Byte Group with out adding row D       0 I tried what I thought would work here but I get a value of 0      

As always thanks a bunch Excel Central

Bill W

 

Attached Files



#7 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 888 posts

Posted 24 February 2014 - 01:43 PM

Hi Bill,

 

You've run across this problem before, in this thread.

 

In order to specify more than one criteria in the same range using SUMIF, you have to use slightly different syntax.

 

You cannot specify B2:B21 twice in a SUMIF function, so this is wrong:

=SUMIFS(C2:C21,A2:A21,"Ace Byte Group",B2:B21,"UK",B2:B21,"USA")

 

If you want to search for both UK and USA in the same SUMIF function, you need to use curly brackets to specify UK and USA.  You also need to surround the function with a SUM function in order to total both the UK and USA figures.

 

Here's the formula that you're looking for:

=SUM(SUMIFS(C2:C21,A2:A21,"Ace Byte Group",B2:B21,{"UK","USA"}))

 

I have attached a copy of your spreadsheet with the correct formula applied:

 

Attached File  SUMIFS2.xlsx   10.24KB   191 downloads

sumifs4.png

 

If you need any more assistance, please feel free to reply to this post.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#8 BillW1971

BillW1971

    Advanced Member

  • Members
  • PipPipPip
  • 35 posts
  • LocationLas Vegas

Posted 24 February 2014 - 11:05 PM

Hello Excel Central,

I feel like such a idiot, I have been trying to figure this out for a week in another post about sumifs. I remember this post and what you said before but I didn't think it worked this way. So I can add as many as I want just by placing them in {s brackets and separating them with ,s and " "s. Well you just made my life a lot easier buddy.

 

Thanks a bunch! 

Bill W







0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users