Madonna House, Westlands Road - Nairobi

How to use the Excel SUMIF Function

How to use the Excel SUMIF Function

SUMIF Excel Function


The SUMIF function is a worksheet function that adds all numbers in a range of cells based on one criteria (for example, is equal to 2000).

The SUMIF function is a built-in function in Excel that is categorized as a Math/Trig Function. It can be used as a worksheet function (WS) in Excel. As a worksheet function, the SUMIF function can be entered as part of a formula in a cell of a worksheet.

To add numbers in a range based on multiple criteria, try the SUMIFS Function

We use the SUMIF function to sum by Category and Invoice Amount. The Categories are Office Supplies, Furniture and Technology


The syntax for the SUMIF function in Microsoft Excel is:

SUMIF( range, criteria, [sum_range] )

Parameters or Arguments


The range of cells that you want to apply the criteria against.


The criteria used to determine which cells to add.


Optional. It is the range of cells to sum together. If this parameter is omitted, it uses range as the sum_range.


The SUMIF function returns a numeric value.

Applies To

Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Type of Function: Worksheet function (WS)

Example (as Worksheet Function)

Let’s explore how to use SUMIF as a worksheet function in Microsoft Excel.

The Range is the category ($B$2:$B$17) while the sum range is the Invoice Amount Column ($C$2:$C$17) while the criteria is on column H. We also need to lock the cells by including the $ sign

Next, add the $ sign by hitting on cell F4 so that it displays as below

The Argument Dialog box with cell references (Absolute reference on the range and sum range

Excel SUMIF Function

Download Sample File

Send download link to:

Subscribe to get exclusive content and recommendations every month. You can unsubscribe anytime.


Leave a Reply

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