Practice SUMIF & SUMIFS Function Online in Excel
Watch: How to use SUMIF & SUMIFS Function in Excel?
What is SUMIF Function?
The SUMIF function in Excel adds up values in a range of cells that meet certain criteria. Click here to Read Full Tutorial
What is SUMIF Function Syntax?
=SUMIF(range, criteria, [sum_range])
Practice 1: SUM The Range in Excel based on Criteria
- Go to cell G3: Click on cell G3 where you want to enter the formula.
- Type
=SUMIF: Start typing=SUMIFto begin the formula. - Select the range: Click and drag to select the column that contains the names of the sales reps (for example, the “Sales REP” column).
- Enter the criteria: After selecting the range, type
"David"in quotation marks, which means you want to look for “David”. - Select the sum range: Now, click and drag to select the column with the sales volume (for example, the “Sales Volume” column).
- Press Enter: Once you’ve selected everything, press Enter. Excel will calculate the sum of sales volumes for “David”
What is SUMIFS Function?
The SUMIFS function in Excel adds up cells that meet multiple criteria. It’s a math and trig function. Click here to Read Full Tutorial
What is SUMIFS Function Syntax?
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...).
Practice 2: Using SUMIFS Function based on multiple criteria
- Go to the cell: Click the cell for the result (e.g., G3).
- Type
=SUMIFS: Start the formula. - Select the sum range: Highlight the numbers to add (e.g.,
$D:$D). - Pick criteria ranges: Highlight the first condition range (e.g.,
$B:$B) and type$F8(e.g., “David”). Do the same for the second range (e.g.,$C:$C) and$G8(e.g., “Australia”). - Press Enter: Get the total.
Practice 3: SUMIFS between Date Range (SUMIFS Logical Characters)
- Click the cell for the result (e.g., G15).
- Type
=SUMIFS(. - Select the column with numbers to sum (e.g., D:D).
- Add the first condition: Select C:C and match F14.
- Add the second condition: Select A:A and check
>=F$12. - Add the third condition: Select A:A and check
<=G$12. - Press Enter to calculate & get the Final Result
Practice 4: SUMIFS when Text Begins With
- Go to the cell: Click the cell where you want the result.
- Type =SUMIFS: This adds numbers based on conditions.
- Select the sum range (D:D): Choose the column with numbers to add.
- Enter the first condition: Select
C:Cand useG$18&"*"to match values starting with the text inG18. - Enter the second condition: Select
B:Band useF19to match the exact value. - Press Enter: Excel will sum numbers in column D matching both conditions.
Practice 5: SUMIFS when Text Ends with
- Go to cell H19 and type =SUMIFS.
- Select D:D for the sum range.
- Select C:C for the first criteria range (Description) and enter the condition “*” & H$18 .
- Select B:B for the second criteria range (Salesperson) and enter the condition F19.
- Press Enter to calculate the total sales volume for the matching rows.
Practice 6: SUMIFS when Text Contains with
- Go to cell H19 and type =SUMIFS.
- Select D:D for the sum range.
- Select C:C for the first criteria range (Description) and enter the condition “*” & I$18 .
- Select B:B for the second criteria range (Salesperson) and enter the condition F19.
- Press Enter to calculate the total sales volume for the matching rows.
