SUMIF Function In Excel With Example

by Imran Khan on Thursday, February 13, 2020 in Excel, Excel for Beginner

SUMIF ()

This function used to create a total from a list of numbers which match criteria set by the user.

Best view in desktop mode.

	A	B	C	D	E	F	G	H
1	Item	Date	Cost
2	Brakes	1-Jan-19	80
3	Tyres	10-May-19	25
4	Brakes	1-Feb-19	80
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 criteria manually or automatically based on value
13
14	Manual
15	Total cost of all Brakes bought.				160	=SUMIF(A2:A10,"Brakes",C2:C10)
16	Total cost of all Tyres bought.				325	=SUMIF(A2:A10,"Tyres",C2:C10)
17	Total of items costing £100 or above.				1000	=SUMIF(C2:C10,">=100")
18
19	Automatic
20	Item	Total Sum
21	Brakes	160	=SUMIF($A$2:$A$10,A21,$C$2:$C$10)
22	Tyres	325
23	Service	450
24	Window	50
25	Clutch	250
26
27
28	Type item and See Total			Tyres	325
29					=SUMIF(A2:A10,D28,C2:C10)
30

$A$2:$A$10 – $ sign before column name and row number is used to fixed the range of data in spreadsheet.

For example, you have put the sumif formula with fixed range in cell C21, then you don't need to type formula for every row or every product. You will need to just copy C21 cell formula and paste in other cells. Like in cell C22 to C25. It will automatically recognize items and calculate their sum.