Filter by Categories
Select all
Budget
Business Statements
Calendars
Federal Income Tax
Gantt Chart
GST India
Health, Logs and Personal Trackers
Inventory
Investments
Invoice/ Bill
Maintenance
Payroll & HR
Project Reports & Timelines
Purchase Order/Requisition
Quotation
Rental Ledger
Risk Registers
Sales/ Marketing
SWOT Analysis
Timesheet/ Attendance
UAE VAT
UK VAT
Filter by File Types






Excel Math Functions : SUMIFS

How to use SUMIFS Math Functions in Microsoft Excel

Introduction to SUMIFS Function

SUMIFS Function returns the sum of the numbers which meet multiple conditions you specify. Unlike SUMIF, you can set multiple conditions and can sum only those values which meet all those conditions.

Download Sample file

Download Sample file for working with the tutorial.

Syntax of SUMIFS Function

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

Arguments in SUMIFS Function

  • [sum_range]: A range of cells & values which you want to sum.
  • criteria_range1: A range from which you want to test criteria.
  • criteria1: A criteria which can be a number, text, expression, cell reference or a function.
  • [criteria_range2]: A range from which you want to test criteria.
  • [criteria2]: A criteria which can be a number, text, expression, cell reference or a function.

Notes

  • The size of the criteria range and sum range should be of the same size.
  • You can also use wildcard characters.
  • If you want to sum values based on single criteria, you can use SUMIF.
  • If you want to specify criteria in the form of text, you have to use double quotation marks.
  • If you skip specifying SUM range it will SUM the values from the criteria range that meet the criteria.

Tutorial of SUMIFS Function in MS Excel

In the below example, we have used three different criteria to check and it has returned the sum of the values which meet those criteria.
In the below example, we have used wildcard characters to specify three different criteria.
  • The first criteria range is A2:A9 and criteria in the cell A12 which will only sum cells that have alphabet O.
  • The second criterion range is B2:B9 and criteria in cell B12 which will only sum cells that have alphabet P. It will apply to all the cells which met condition 1.
  • The third criterion range is C2:C9 and criterion in cell C12 which will sum cells with any type of value. It will apply to all the cells which met the condition 1 and 2.

Other Math Function in Microsoft Excel

Explore other math function in Microsoft excel. The list of all the math function available for everyday use is given below –

Content

Macro Codes in Excel

Learn Excel Quickly
Get full list of Excel Macro Codes