SUMIFS Function in Excel with Example

SUMIFS ()

This function used to create a total from a list of numbers which match multiple criteria (more than one criteria) set by the user.

Best view in desktop mode.

	A	B	C	D	E	F	G	H	I
1	Item	Date	Cost
2	Brakes	1-Jan-19	80
3	Tyres	10-May-19	25
4	Brakes	1-Feb-19	100
5	Service	1-Mar-19	150
6	Service	5-Jan-19	300
7	Window	1-Jun-19	50
8	Tyres	1-Apr-19	200
9	Tyres	1-Mar-19	100
10	Clutch	1-May-19	250
11
12	You can define multiple criteria manually or automatically based on value
13
14	Manual
15	Total cost of all Brakes & Clutch bought.				430	=SUMIFS(C3:C11,A3:A11,"Brakes",A3:A11,"Clutch")
16	Total cost of all Brakes bought on 1-Feb-19.				100	=SUMIFS(C3:C11,A3:A11,"Brakes",A3:A11,"1-Feb-19")
17
18
19	Multiple criteria on single column Item
20	Automatic
21	Type two item and See Total			Tyres	775
22				Service
23						=SUMIFS(C3:C11,A3:A11,D21,A3:A11,D22)
24
25	Multiple criteria on multiple columns Item and Date
26	Automatic
27	Type item and Date		Item	Service	300
28			Date	5-Jan-19
29						=SUMIFS(C3:C11,A3:A11,D27,B3:B11,D28)
30

Like above example, you can add multiple criteria on multiple columns.

Syntax

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

Formatting

No special formatting is needed.

For example

In above example for automatic calculation with item and date:

=SUMIFS(C3:C11,A3:A11,D27,B3:B11,D28)

C3:C11 – column cost range (common range for all criteria) to examine sum of item.

A3:A11 – column Item range (criteria 1 range) to examine name of item in it.

D27 – item selection cell (criteria 1) to identify the entry of item i.e. Service

B3:B11 - column date range (criteria 2 range) to examine date in it.

D28 – date selection cell (criteria 2) to identify the entry of date i.e. 5-Jan-19

SUMIF in Excel

<<  Previous

Next  >>

LEFT in Excel