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.
So in the example spreadsheet above, we want to know how many spades have been sold in total. So we enter the formula:
This returns the answer: 2200 spades sold.
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, ...)
We want to know the number of Spades sold in Newcastle so we enter the following formula:
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