it training solutions    
IT Training Solutions Ltd
AA

MS Excel Complex Functions - SUMIF


How to use Excel Functions - SUMIF

The SUMIF function allows you to add cells that meet a certain criteria. The function has the following format:

Formula syntax: =SUMIF(range, criteria, [sum_range])

• Range: Here you need to specify which cells will be evaluated

• Criteria: This is the condition that will be used to select the cells from the Range, thus showing which numbers to add. The criteria take the form of a number, text or expression.

• Sum_range: This defines the actual cells you wish to add together as defined in the corresponding Range. If you omit the Sum_range then the cells in the range are added together.

Example:

SUMIF Function Example in Excel

So in the example spreadsheet above, we want to know how many spades have been sold in total. So we enter the formula:
=SUMIF(B4:B11,?Spade?,C4:C11)

This returns the answer: 2200 spades sold.

SUMIFS function

The SUMIFS function extends the SUMIF function by allowing you to set conditions on two or more ranges of data rather than just the one in SUMIF.

SUMIFS looks for specific criteria in two or more fields in the record and only if it finds a match for each field specified is the data for that record added up. The formula syntax for the SUMIFS function is:

=SUMIFS(Sum_range,Criteria_range1, Criteria1, Criteria_range2, Criteria2, ...)

Example:

SUMIFs Function in Microsoft Excel



We want to know the number of Spades sold in Newcastle so we enter the following formula:

=SUMIFS(D3:D10,B3:B10,"Spade",C3:C10,"Newcastle").

This returns the answer: 1633 spades have been sold in Newcastle.

Up to 127 range/criteria pairs can be entered into the function.


This information relates to Excel 2010 Level 3



Please note that all tutorials and resources provided on this site remain the property of IT Training Solutions Ltd. These are free resources that may be used for personal or business use, but may not be shared, copied or redistributed other than by the tools given precisely on the relevant web page. By using this section of the site, you agree to adhere to these rules. IT Training Solutions Ltd does not warrant that all content and resources will be error free or that defects will be corrected, these resources are given free of charge and IT Training Solutions Ltd cannot be held responsible for any negative effects that occur from using these resources. For more information or to ask us a question, please contact us on 0191 377 8377. Thank you.