Hàm: Sum, SumIf, SumIfs, Dsum

Hàm SUM

Hàm SUM có hai cấu trúc do đó có thể được viết theo hai cách:

• =SUM(num1,num2, ... num_n). Ở đây " num1,num2, ... num_n" biểu thị cho những số chúng ta sẽ tính.
• =SUM(CellRange1,CellRange2,...CellRange_n). Trong đó: "CellRange1,CellRange2,... CellRange_n" là những cột số.

Những chú ý trong phép tính SUM:

• Hàm SUM có thể tính số thập phân và phân số.

• Trong phép tính SUM: =SUM(num1,num2, ... num_n), nếu bạn nhập tham số "num" không phải là số thì hàm SUM sẽ hiện thị lỗi #Name? Error.

• Và tương tự như vậy, ở phép tính =SUM(CellRange1,CellRange2, ...CellRange_n) chứa nội dung không phải số thì hàm SUM sẽ bỏ qua giá trị đó.

• Hàm SUM không phải là dạng hàm động lực học. Vì vây, nếu bạn sử dụng phép tính SUM vào dãy các ô rồi lọc ra một vài giá trị nào đó thì đầu ra của phép tính SUM sẽ không thay đổi theo gía trị gần nhất. Do đó tốt hơn hết, bạn nên dùng hàm Subtotal cho trường hợp này.

Những ví dụ về hàm SUM

Ở bảng trên, có bốn loại của hàm SUM và dưới đây tôi sẽ giải thích từng ví dụ cụ thể:

1. Ví dụ 1, tổng các số nguyên: =SUM(10,11,19) nghĩa là cộng các số 10,11,19 và 40 là kết quả 40.

2. Ví dụ 2, tổng các số thập phân: =SUM(10.2,9.6,2,4), đơn giản là phép tính cộng của 10,2+9.6+2.4 và cho kết quả 25.8.

3. Ví dụ 3, tổng các số phân số: =SUM(4/2,8/2). Phép tính cộng các số 4/2 và 8/2 và kết quả là 6.

4. Ví dụ 4, hàm SUM bị lỗi do có chứa nội dung không phải số và đó cũng là lý do phép tính cho ra #Name? Error.

5. Sử dụng dạng thứ hai của hàm SUM [=SUM(B2:B10)] để tính tổng cho các số trong dãy số (B2:B10) thay cho các tham số và cho ra kết quả 60.

Rút gọn của phép tính SUM:

Thay vì viết phép tính theo cách đơn thuần, bạn có thể sử dụng cách sau:

Đầu tiên, lựa chọn dãy số rồi ấn "Alt +" và kết quả sẽ hiển thị ở ô tiếp theo.

Hàm SUMIF

SUMIF là hàm dùng để tính tổng các giá trị thỏa mãn điều kiện nhất định, được giới thiệu lần đầu trong Excel 2007. Hàm SUMIF có thể sử dụng cho việc tính tổng các ô dựa vào ngày, số liệu và văn bản khớp với điều kiện đã cung cấp. Hàm này hỗ trợ các phép tính logic (>, <, <>, =) và ký hiệu (*,?) cho phù hợp với từng phần.

Bạn hẳn sẽ thắc mắc, hàm SUMIF khác gì hàm SUM? Hàm SUMIF mở rộng khả năng của hàm SUM, thay vì tính tổng trong một phạm vi nhất định, để được tính tổng trong SUMIF các ô buộc phải thỏa mãn điều kiện mà người dùng truyền vào tham số criteria. Hàm SUMIF tiết kiệm rất nhiều công sức nếu bạn muốn tính tổng doanh thu của một đơn vị, doanh số của một nhóm nhân viên, hoặc doanh thu trong khoảng thời gian nhất định, tổng lương theo điều kiện nào đó...

Mục đích của hàm SUMIF là để tính tổng các số trong phạm vi đáp ứng được các tiêu chí đã cung cấp. Kết quả trả về là tổng các giá trị đã cung cấp.

Công thức hàm SUMIF

=SUMIF (range, criteria, [sum_range])

Tham số của hàm SUMIF:

• Range: Phạm vi các ô bạn muốn đánh giá theo tiêu chí Criteria. Các ô trong mỗi phạm vi phải là số hoặc tên, mảng hay tham chiếu chứa số. Giá trị trống và giá trị văn bản bị bỏ qua. Phạm vi được chọn có thể chứa các ngày ở định dạng Excel tiêu chuẩn.
• Criteria: Tiêu chí xác định các giá trị được tính tổng. Nó có thể là số, biểu thức hoặc một chuỗi văn bản.
• Sum_range: Tham số này là tùy chọn, nó sẽ chỉ ra các ô cần tính tổng. Nếu để trống sum_range, những ô trong phạm vi đánh giá sẽ được thay thế.

Ví dụ và cách sử dụng SUMIF trong Excel

Hãy cùng Quantrimang.com học cách sử dụng hàm SUMIF nhé. Giả sử chúng ta có bảng tổng kết thu nhập và thuế thu nhập cá nhân như sau:

Tổng thu nhập	Số người phụ thuộc	Thu nhập chịu thuế	Thuế TNCN	Thu nhập thực nhận	Phòng ban
80,000,000	2	63,348,500	13,154,550	66,393,950	Kế toán
30,000,000	1	16,948,500	1,792,275	27,756,225	Bán hàng
20,000,000	1	6,875,000	437,500	19,037,500	Bán hàng
10,000,000	1	-3,650,000	0	8,950,000	Bán hàng
100,000,000	2	82,750,000	19,112,500	79,837,500	Kế toán
1,000,000,000	1	976,900,000	332,065,000	657,435,000	Giám đốc

Trong Excel bảng được trình bày như sau:

Ví dụ 1: Sử dụng hàm SUMIF để tính tổng thuế thu nhập cá nhân của những người có thu nhập dưới 50 triệu đồng.

Để giải quyết ví dụ này, trước tiên ta cần xác định được 3 tham số của hàm SUMIF, cụ thể:

• Range: Phạm vi cột chứa tổng thu nhập, ở đây là B4:B9
• Criteria là <50.000.000, vì có chứa kí hiệu toán học, nên cần đặt trong "': "<50.000.000"
• Sum_range là phạm vi cột chứa thuế thu nhập cá nhân cần tính tổng, ở đây là H4:H9

Khi đó, ta sẽ có công thức: =SUMIF(B4:B9,"<50.000.000",H4:H9), bạn nhập công thức này vào ô chứa kết quả, trong ví dụ của chúng tôi là ô H19.

Nhấn Enter và sẽ nhận được số tiền thuế thu nhập cá nhân cần đóng cho những người có thu nhập dưới 50.000.0000 là 2.229.775.

Ví dụ 2: Vẫn với bảng tính trên, hãy tính tổng thuế thu nhập cá nhân của người có 2 người phụ thuộc. Lúc này:

• Range: Phạm vi cột chứa số người phụ thuộc, ở đây là D4:D9
• Criteria là =2, có thể viết là 2 hoặc "=2"
• Sum_range là phạm vi cột chứa thuế thu nhập cá nhân cần tính tổng, ở đây là H4:H9.

Như vậy, công thức sẽ là: =SUMIF(D4:D9,2,H4:H9), bạn nhập công thức vào ô chứa kết quả, trong ví dụ là H21:

Nhấn Enter để nhận được tổng thuế trả về là 32.267.050 đồng.

Ví dụ 3: Vẫn trong bảng trên, tính tổng thu nhập của những người có thu nhập trên 80 triệu đồng. Lúc này:

• Range: Phạm vi cột chứa tổng thu nhập, ở đây là B4:B9
• Criteria là lớn hơn 80 triệu,viết là ">80000000"
• Sum_range trong trường hợp này chính là Range nên bạn không cần nhập tham số này nữa.

Như vậy công thức sẽ là: =SUMIF(B4:B9,">80000000"), bạn nhập vào ô chứa kết quả, ở đây là H22:

Nhấn Enter để nhận kết quả tổng thu nhập của những người có thu nhập trên 80 triệu là 1.100.000.000:

Ví dụ 4: Vẫn dùng bảng số liệu ban đầu, ta đi tính tổng thu nhập của phòng bán hàng nhé. Khi đó:

• Range: Phạm vi cột chứa danh sách phòng ở đây là J4:J9
• Criteria là Bán hàng, viết là "Bán hàng"
• Sum_range là phạm vi cột chứa thu nhập cần tính tổng, ở đây là B4:B9

Như vậy công thức sẽ là: =SUMIF(J4:J9,"Bán hàng",B4:B9), bạn nhập công thức vào ô chứa kết quả, như hình dưới là ô H23:

Nhấn Enter và nhận về tổng thu nhập của phòng bán hàng là 60.000.000:

Chú ý khi sử dụng hàm SUMIF

• Khi sum_range bị bỏ qua, tổng sẽ được tính theo Range.
• Criteria có chứa chữ hay ký hiệu toán học đều phải được đặt trong dấu ngoặc kép "".
• Phạm vi xác định dạng số có thể được cung cấp là số sẽ không phải dùng đến dấu ngoặc.
• Các ký tự ? và * đều có thể được sử dụng trong Criteria. Một dấu chấm hỏi khớp với bất kỳ ký tự đơn nào; một dấu sao phù hợp với bất kỳ chuỗi ký tự nào. Nếu bạn muốn tìm một dấu chấm hỏi hay dấu sao thực sự, hãy gõ dấu ngã (~) trước ký tự.

Hàm SUMIFS

Hàm SUMIFS trong Excel là một trong những hàm Excel cơ bản, hàm tính toán thường được dùng trong Excel. Để tính tổng trong Excel chúng ta sẽ dùng đến hàm SUM, nếu muốn thêm 1 điều kiện nhất định cho hàm tính tổng đó sẽ dùng hàm SUMIF. Trong trường hợp bảng dữ liệu yêu cầu tính tổng kèm theo nhiều điều kiện, thì chúng ta phải sử dụng tới hàm SUMIFS. Bài viết dưới đây sẽ hướng dẫn bạn đọc cách sử dụng hàm SUMIFS trong Excel.

Hướng dẫn dùng hàm SUMIFS trong Excel

Cú pháp hàm SUMIFS trong Excel có dạng =SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2,…).

Trong đó:

• Sum_range: là các ô cần tính tổng trong bảng dữ liệu, những giá trị trống và giá trị text được bỏ qua, tham số bắt buộc phải có.
• Criteria_range1: phạm vi cần được kiểm tra bằng điều kiện criteria1, là giá trị bắt buộc.
• Criteria1: điều kiện áp dụng cho criteria_range1, có thể là một số, biểu thức, tham chiếu ô để xác định ô nào trong criteria_range1 sẽ được tính tổng, cũng là giá trị bắt buộc.
• Criteria_range2,criteria2,…: tùy chọn các phạm vi và điều kiện bổ sung, tối đa 127 cặp criteria_range, criteria.

Lưu ý khi dùng hàm SUMIFS trong Excel:

• Mỗi ô trong vùng, phạm vi sum_range sẽ được cộng tổng khi thỏa mãn tất cả các điều kiện tương ứng đã xác định là đúng cho ô đó.
• Các ô trong sum_range có chứa TRUE được coi là 1, các ô chứa FALSE được coi là 0.
• Mỗi criteria_range phải có cùng kích thước vùng chọn giống với sum_range, criteria_range và sum_range phải có cùng số hàng và cùng số cột.
• Criteria có thể dùng ký tự dấu hỏi chấm (?) thay cho ký tự đơn, dấu sao (*) thay cho một chuỗi. Nếu điều kiện là dấu hỏi chấm hay dấu sao thì phải nhập thêm dấu ~ ở phía trước, giá trị điều kiện là văn bản để trong dấu “.

Chúng ta sẽ tính tổng với bảng dữ liệu dưới đây kèm theo một số điều kiện khác nhau.

Ví dụ 1: Tính tổng sản phẩm mà nhân viên Hoài bán ra có đơn giá mặt hàng dưới 400.000đ.

Tại ô nhập kết quả chúng ta điền công thức =SUMIFS(D2:D7,C2:C7,"Hoài",E2:E7,"<400000") rồi nhấn Enter.

Trong đó:

• D2:D7 là vùng cần tính tổng mặt hàng.
• C2:C7 là vùng giá trị điều kiện tên nhân viên.
• “Hoài” là điều kiện tên nhân viên cần tính số mặt hàng chứa trong vùng C2:C7.
• E2:E7 là điều kiện mặt hàng.
• “400.000” là điều kiện chứa mặt hàng trong vùng E2:E7.

Kết quả ra tổng số lượng mặt hàng như hình dưới đây.

Ví dụ 2: Tính tổng số lượng mặt hàng bán ra của nhân viên Nga có số thứ tự < 5.

Tại ô nhập kết quả chúng ta nhập công thức =SUMIFS(D2:D7,C2:C7,"=Nga",A2:A7,"<5") rồi nhấn Enter. Tại tên nhân viên "Nga" nhập dấu = hoặc bỏ dấu = đều được.

Kết quả chúng ta được tổng các mặt hàng do nhân viên Nga bán có số thứ tự < 5 là 550 mặt hàng.

Ví dụ 3: Tính tổng số lượng sản phẩm đã bán ra của nhân viên Nga, trừ sản phẩm khăn quàng cổ.

Tại ô nhập kết quả, chúng ta nhập công thức =SUMIFS(D2:D7,C2:C7,“Nga”,B2:B7,“<>khăn quàng cổ”). Trong đó dấu <> dùng để loại trừ một đối tượng nào đó trong vùng dữ liệu làm điều kiện.

Kết quả cho ra tổng số sản phẩm như hình dưới đây.

Trên đây là một số ví dụ về cách sử dụng hàm SUMIFS, tính tổng giá trị có nhiều điều kiện kết hợp. Chúng ta phải sắp xếp các vùng và điều kiện đi kèm hợp lý thì Excel mới nhận dạng được công thức hàm để tính.

Hàm DSUM

Trong các hàm cơ bản Excel thì hàm DSUM là một trong những hàm thông dụng. Hàm này sẽ tính tổng của một trường, hoặc 1 cột để thỏa mãn điều kiện mà bạn đưa ra trong bảng dữ liệu. Công thức sử dụng hàm DSUM cũng như cách tính tổng có điều kiện của hàm tương đối đơn giản và nhanh hơn so với hàm SUMIF trên Excel. Bài viết dưới đây sẽ hướng dẫn bạn đọc cách sử dụng hàm DSUM trên Excel để tính tổng cột với điều kiện cho trước.

Hướng dẫn tính tổng có điều kiện bằng DSUM

Hàm DSUM là hàm cộng các số trong một trường, cột trong danh sách dữ liệu thỏa mãn với các điều kiện xác định. Cú pháp hàm là DSUM =DSUM(database; field; criteria).

Trong đó:

• Database là đối số bắt buộc, là cơ sở dữ liệu được tạo từ 1 phạm vi ô. Danh sách dữ liệu này sẽ chứa các dữ liệu là các trường, gồm trường để kiểm tra điều kiện và trường để tính tổng. Danh sách chứa hàng đầu tiên là tiêu đề cột.
• Field là đối số bắt buộc chỉ rõ tên cột dùng để tỉnh tổng các số liệu. Có thể nhập tên tiêu đề cột trong dấu ngoặc kép, hoặc dùng 1 số thể hiện vị trí cột trong danh sách không dùng dấu ngoặc kép, hay tham chiếu tới tiêu đề cột muốn tính tổng.
• Criteria là đối số bắt buộc, phạm vi ô chứa điều kiện muốn hàm DSUM kiểm tra.

Lưu ý người dùng

• Có thể dùng phạm vi bất kỳ cho criteria nếu phạm vi đó chứa ít nhất 1 nhãn cột và ít nhất 1 ô bên dưới tiêu đề cột mà sẽ xác định điều kiện cho cột đó.
• Không đặt phạm vi điều kiện ở phía dưới danh sách vì sẽ không có vị trí thêm các thông tin khác vào danh sách.

Chúng ta sẽ xử lý bảng dữ liệu như hình dưới đây.

1. Tính tổng số tiền đã bán của sản phẩm iPhone

Bước 1:

Trước hết chúng ta tạo phạm vi điều kiện cho hàm DSUM để tính tổng số tiền đã bán của các sản phẩm iPhone. Điều kiện cho hàm DSUM là iPhone*.

Bước 2:

Tiếp đến người dùng nhập công thức hàm DSUM là =DSUM(A6:E13;"Thành Tiền";C2:C3).

Trong đó:

• A6:E13 là phạm vi cơ sở dữ liệu chứa cột cần tính tổng và cột chứa điều kiện cần kiểm tra.
• “Thành Tiền” là tiêu đề cột sẽ sử dụng giá trị trong cột đó để tính tổng.
• C2:C3 là phạm vi điều kiện chứa tiêu đề cột và 1 giá trị điều kiện.

Nhấn Enter và chúng ta sẽ được kết quả chính xác như hình.

Hoặc người dùng có thể thay giá trị trong Field thành tham chiếu tới cột Thành tiền. Công thức nhập là =DSUM(A6:E13;E6;C2:C3). Kết quả cũng tương tự như khi bạn nhập cột Thành tiền vào công thức.

2. Tính tổng số tiền sản phẩm đã bán có số lượng lớn hơn hoặc bằng 5.

Bước 1:

Đầu tiên người dùng cũng sẽ cần tạo phạm vi điều kiện với tiêu đề cột Số lượng bán và giá trị điều kiện là >=5 như hình dưới đây.

Bước 2:

Chúng ta nhập công thức hàm là =DSUM(A6:E13;E6;C2:C3) và nhấn Enter.

Kết quả tổng số tiền sẽ như trong hình.

Nhìn chung cách sử dụng hàm DSUM trên Excel vô cùng đơn giản. Bạn có thể sử dụng hàm để tính tổng các giá trị theo cột với điều kiện đơn giản. Với những điều kiện phức tạp hơn chúng ta có thể sử dụng hàm SUMIF.