Sumif function in Excel

SUMIF function in Excel uses the combined power of two functions: sum and if and adds the cells specified by a given criteria.

Syntax: SUMIF(range, criteria, sum_range)

Range is the range of cells that you want evaluated by criteria.  Criteria is the criteria in the form of a number, expression, or text that defines which cells will be added. For example, criteria can be expressed as 32, “32”, “>32”, or “yes”.  Sum_range are the actual cells to add if their corresponding cells in range match criteria. If sum_range is omitted, the cells in range are both evaluated by criteria and added if they match criteria.  You can use the wildcard characters, question mark (?) and asterisk (*), in criteria. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) preceding the character.

Microsoft Office Excel provides additional functions that you can use to analyze your data based on a condition or criteria:  To count the number of occurrences of a string of text or a number within a range of cells, use the COUNTIF function.  To have a formula return one of two values based on a condition, such as a sales bonus based on a specified sales amount, use the IF function.  To analyze data in a list based on criteria, such as profit margins or product types, use the database and list management functions (DAVERAGE, DCOUNT, DCOUNTA, DGET, DMAX, DMIN, DPRODUCT, DSTDEV, DSTDEVP, DSUM, DVAR, and DVARP).  Many ‘invoice’ templates have been designed using ‘sumif’ where some items having ‘vat’ are summed and those having no vat are summed seperately to get the desired total.  See the sumif Excel function in action in our training video below.

Further reading

MS Excel: SUMIF Function (WS)

Excel SUMIF Function Quick Tutorial

Leave a Reply

Your email address will not be published. Required fields are marked *