## Do you know the difference between the SUMIF and SUMIF**S** function?

The SUMIF allows only one condition to be evaluated, whereas the SUMIFS (introduced in Excel 2007) allows up to 29 criteria to be evaluated. It’s the plural version of the SUMIF function.

First, let’s cover the SUMIF function. It sums the data in a column or row based on a condition.

In the example below, SUMIF adds the values in Column B, if Column A equals Company A.

The syntax of the SUMIF function is comprised of the 3 following arguments:

- the criteria range – in this case A2:A8
- the criteria itself – in this case Company A
- the sum range – in this case column B2:B8

What if you have multiple conditions? How do you get the total sales for Product A, sold by Company A? This is where the SUMIFS function comes in.

The syntax of SUMIFS is different to the SUMIF function. SUMIFS was introduced in Excel 2007. Notice for the SUMIF function, the sum range is at the end. It’s not possible to put the sum range at the end for the SUMIFS because the number of criteria to be evaluated can vary. This means the position of the sum range in the formula can vary. For this reason, the sum range was switched to the first argument in the function. The original SUMIF formula couldn’t be revised using this new format because it would cease to be compatible with older Excel versions.

The syntax of the SUMIFS function is

- First is the sum range
- Next the criteria range of your first criteria
- Followed by the criteria itself
- Add more criteria ranges followed by the criteria as necessary

The below example shows the formula that finds the total sales of Company A for Product A.

Notice cell references have been made for the company and product.

You can include in the criteria argument

- wildcards in the form of *
- mathematical operators such as >, <, =, or a combination of these

You need to surround the mathematical operators in quotation marks. If you wanted total sales for Company A and Product A, where sales are greater than 425, you would need to add additional criteria. The criteria range is the sales value which is also your sum value. The “>425” can be written in different forms. It can also be written as “>”&425 or “>”&C7. C7 is the cell reference for the value 425 .

The same logic applies to the AVERAGEIFS function, which as opposed to the sum function calculates the average value of the range based on given criteria.

COUNTIFS function is shorter because it doesn’t need the additional sum range. It only needs the criteria range. It counts the number of times the criteria are met.

As you can see, the IFS version allows you to easily add exceptions to your sum, average and count calculations.

**Don’t make your life more difficult by trying to use Excel filters to get your sum, use the IFS formula instead.**

### Watch the steps in this video:

Feel free to Download the Workbook HERE.

Priscilla BellFebruary 10, 2018 at 4:37 pmI have to keep track of teacher qualifications, ages & gender for our Department of Education. There are 5 School Boards.

Once I input the data, I want to be able to have the data separate out for each School Board

Headers are: Board School; Gender; Last Name; Maiden Name; First name(s); Date of Birth; Age; Actual Age; Country; Nationality; Besluit/contract; Diploma on file; FBE C1 Trained; FBE C2 Trained; Qualified;

On the pages for school boards, I want an overview of 1. ages (highest, lowest, average) 2. total female teachers 3. total male teachers 4. total qualified teachers

Is it possible once I input all the data that I can pull it out like this? Right now I ma doing it manually and I would rather not as errors do happen.

What can I use? SUMIFS, COUNTIFS, a pivot table??

Please advise

Hasnain AkNovember 9, 2018 at 1:16 pmExcellent Tutor! indeed

Fatima NazneedMarch 27, 2019 at 11:09 pmYou are a very good, as well as a generous educator. Providing your learners with the workbook is really something that would not go unappreciated even by the worst ungrateful person in the world.

Leila GharaniMarch 28, 2019 at 5:54 pmThank you Fatima! I’m glad you find the tutorials here useful.

RomeelApril 3, 2019 at 9:55 amThank You So Much For Your Efforts!

KarinMay 5, 2019 at 6:16 amYou are an excellent teacher. Help me a lot. Thanks a mil.

kdMay 5, 2019 at 6:20 pmLeila, you are truly an inspiration to us all. Your excellent Excel videos saved my career. I cannot adequately thank you enough for everything. I am beyond forever grateful to you for the lecture videos. Looking forward to your future videos that are ALWAYS easy to understand

Leila GharaniMay 9, 2019 at 5:38 pmYour comment made my day! Thank you very much kd. I’m very happy to hear you find the content useful.

HubertJune 28, 2019 at 4:06 pmExcellent video – as always. Really like your calm and approachable teaching style.

Munichandra ReddyAugust 21, 2019 at 12:07 pmHey

Greeting’s!!!!

Your Teaching was awesome !! But You Didn’t provide practice Workbooks .if You Don’t Mind Can You Provide Practice Workbook With Mail Id ?

Bryon SmedleyAugust 21, 2019 at 8:02 pmThe workbook link is posted at the bottom of the blog post.

OlaySeptember 6, 2019 at 9:13 amWould you please show me if we want total sale in Feb, Mar and Apr for Shirt White in USA and UK.

Thank you

Leila GharaniSeptember 12, 2019 at 8:02 amYou can select all the months as the criteria – but since SUMIFS would return 3 results, you can nest it inside the SUMPRODUCT function. Another way is to make 3 sumifs and add the results together.

EmmanuelSeptember 12, 2019 at 4:54 pmthanks for your patience in teaching

Keep it up Teacher