Tính Sumif Trong Excel / TOP 10 # Xem Nhiều Nhất & Mới Nhất 6/2023 # Top View

Hàm sumif trong excel là một hàm được sử dụng để tính tổng có điều kiện trong excel. Có thể áp dụng hàm sumif trong excel trong các phiên bản Excel 2003, Excel 2007, Excel 2010, Excel 2013. Bài viết này Hiếu sẽ giới thiệu cụ thể cho các bạn về khái niệm và định nghĩa, cách sử dụng,… Các bạn kế toán cần ghi nhớ để có thể vận dụng và mang lại hiệu qủa cao nhất trong công việc.

1, Định nghĩa, cú pháp và ví dụ về hàm sumif trong excel:

Hàm sumif trong excel là hàm tính tổng có điều kiện được sử dụng khá nhiều trong kế toán. Hàm này giúp tính toán các số liệu một cách chính xác và hiệu quả trong các công việc của bạn.

1.1, Định nghĩa

Hàm sumif trong excel là hàm excel được sử dụng để tính tổng có 01 điều kiện trong excel.

Hàm sumif trong excel thường được dùng trong kế toán để thực hiện các công việc sau:

Tính tổng tiền phụ cấp cho nhân viên.

Tính lương nhân viên theo các điều kiện nào đó.

Kết chuyển các bút toán cuối kỳ.

Lập bảng cân đối phát sinh trong tháng/năm, hay bảng nhập xuất tồn kho,…từ bảng nhập liệu.

1.2, Ví dụ về hàm Sumif

Tính tổng lương theo từng cấp bậc:

Đây là ví dụ về 10 người nhưng áp dụng cho danh sách trên 10.000 người. Không cần công tay mất thời gian và có được độ chính xác cực cao.

1.3, Cú pháp hàm SUMIF trong excel:

=SUMIF(range, criteria, [sum_range]) hay SUMIF( Vùng điều kiện, Điều kiện, Vùng tính tổng)

Giải thích hàm:

=SUMIF(….): Là cú pháp lệnh bắt buộc.

Range (bắt buộc): Là vùng chọn ( cùng điều kiện) có chứa các ô điều kiện. ở ví dụ là cột E5-E10 chứa dánh sách chức vụ từng người.

Criteria (bắt buộc): Là điều kiện, cấp bậc nên có thể ở dạng số, dạng chữ hay ô tham chiếu, biểu thức hoặc hàm xác định sẽ cộng những ô nào. Ví dụ như tính tổng lương cho trưởng phòng thì điều kiện là ” Trưởng phòng”

Sum_range (tùy chọn): Vùng chọn thực tế mà các bạn cần tính tổng. Ở ví dụ này là cột Tổng thực lĩnh G5 đến G14.

Các bước cụ thể sử dụng hàm SUMIF trong Excel:

Bước 1: Xác định rồi chọn vùng chứa các ô điều kiện

Bước 2: Nhập vào hàm điều kiện

Bước 3: Chọn vùng chọn để thỏa mãn điều kiện mà công việc yêu cầu.

Lúc này Hàm sumif sẽ được viết như sau:

1.4, Hàm sumif trong excel có 2 cách viết:

Khi thấy ở trên có 2 cách viết tính tổng lương cho các cấp bậc trong công ty, khác nhau về ĐIỀU KIỆN tính tổng.

Cách 1: Tại điều kiện tính tổng ta sử dụng chuỗi Text. Các chuỗi text trong excel thì ta để trong dấu ” “.

Cách 2: Sử dụng địa chỉ ô thay vì nhập chuỗi Text. Viết địa chỉ chứa giá trị của chuỗi text như trưởng phòng là K11.

Các bạn nên ghi nhớ và sử dụng cách thứ hai vì đơn giản hơn và giúp các bạn tiết kiệm thời gian hơn.

2, Các lưu ý quan trọng khi sử dụng hàm sumif trong excel:

2.1, Thêm dấu $ trong công thức tai vùng điều kiện và vùng tính tổng:

Trong excel chúng ta sử dụng dấu $ để cố định dòng hoặc cố định cột. Nên khi sử dụng chúng ta chỉ cần viết công thức cho ô thứ nhất sau đó copy cho các ô còn lại rất nhanh chóng.

Khi các bạn không sử dụng dấu $ để cố định vùng tính tổng và vùng điều kiện. Thì địa chỉ vùng sẽ bị thay đổi khi copy sang dòng hay các cột khác và sai kết quả.

2.2, Cách tính tổng với điều kiện bằng, lớn hơn hoặc nhỏ hơn một giá trị cho trước.

Ví dụ: nếu muốn tính lương với những người có mức lương < 5.000.000. Thì ta sẽ làm như sau:

Sử dụng hàm sumif trong excel để tính tổng các điều kiện lớn hơn, bằng hay nhỏ hơn như sau:

= SUMIF( G5:G14, “<“&5000000,”G5:G14)= 10.800.000

2.3, Dùng hàm sumif trong excel để tính tổng các giá trị khác 1 giá trị nào đó:

Ví dụ: Yêu cầu tính lương của tất cả công ty trừ những người có cấp bậc Trưởng phòng.

Yêu cầu: tính tổng với điều kiện KHÁC Trưởng phòng.

Và viết hàm sumif trong excel để tính tổng như sau:

2.4, Tính tổng nâng cao với dấu (*)

Ví dụ: tính tiền lương của những người mang họ Phạm. Cái này sẽ khó hơn vì có nhiều người mang họ Phạm khác nhau.

Xử lý: Ta kết hợp dấu * ngay sau chữ Phạm tại điều kiện.

Cụ thể:

= SUMIF(D5:D14, “Phạm*”, G5:G14)= 20.500.000

Tương tự như vậy thì chúng ta có thể dùng công thức để tính tiền lương của Nhân viên theo vùng điều kiện là cột Mã nhân viên. Không cần sử dụng cột cấp bậc.

Công thức:

Tổng tiền lương nhân viên thực lĩnh: = SUMIF(C5:C14,”NV*”,G5:G14)

3, Vận dụng hàm sumif trong excel vào thực tế:

Hàm sumif trong excel được sử dụng khá nhiều và mang lại giá trị thực tế cho kế toán, nhân sự,…

Các bạn làm nhân sự yêu cầu phải báo cáo tổng số nhân sự là bao nhiêu? Nhân sự kinh doanh tháng này và tháng trước chênh lệch như thế nào. Hay chi phí các phân xưởng A, B của tháng này là bao nhiêu,…

Lúc này bạn sẽ cần danh sách chi tiết lương toàn công ty. Rồi sau đó dựa vào từng điều kiện phòng hay phân xưởng yêu cầu để tính lương hoặc số lượng nhân sự.

Sử dụng hàm sumif trong excel để tính:

Tổng tiền lương phòng A = Sumif( Cột phòng ban, “phòng A”, cột thực lĩnh)

Tổng lương của phân xưởng B= Sumif ( Cột phân xưởng, “Phân xưởng B”, Cột lương thực lĩnh)

Số lượng nhân sự Công đoạn dệt= Sumif ( Cột công đoạn, ” Dệt”, Cột số lượng nhân sự.

Ứng dụng về tổng hợp báo cáo nhập xuất tồn hàng hóa:

Trong một kì thì sẽ nhập nhiều hàng hóa và để thống kê số lượng hay doanh thu từng mặt hàng. Các bạn sẽ sử dụng hàm tính tổng có điều kiện.

Sử dụng hàm sumif trong excel như sau:

Tổng số lượng của mặt hàng A= Sumif ( CỘt mã hàng, mã hàng A, cột số lượng)

Tổng doanh thu ngày 15= Sumif ( Cột ngày tháng, ngày 15, cột doanh thu)

Thực chất hàm SumIFs là hàm tính tổng có điều kiện nâng cấp của hàm SumIF, nếu như SumIF tính tổng với 1 điều kiện thì SumIFs cho phép ta tính tổng với nhiều điều kiện. Trong bài viết này, chúng ta sẽ cùng tìm hiểu cách dùng hàm tính tổng có điều kiện Sumif và SumIFs.

Cách dùng hàm SUMIF để tính tổng có điều kiện trong Excel

Công dụng: Hàm SUMIF là hàm tính tổng có điều kiện trong Excel

Cú pháp (syntax): =SUMIF (range, criteria, [sum_range])

Trong đó

Range: Phạm vi các ô bạn muốn đánh giá theo tiêu chí. 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ị thêm vào.

Sum_range: (tùy chọn) Các giá trị được gắn thêm. Nếu sum_range bị bỏ quá, những ô trong phạm vi đánh giá sẽ được thay thế.

Chú ý:

_ Khi sum_range bị bỏ qua, các ô trong phạm vi sẽ được cộng lại.

_ Khu vực xác định 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 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ự.

Lưu ý: Để làm theo hướng dẫn các bạn có thể tải file Tại Đây

Một ví dụ cách sử dụng hàm SUMIF đểTính tổng cột Số lượng với Tên hàng là Red Apples, như vậy chúng ta nhập công thức như hướng dẫn hình sau:

Tính tổng các giá trị LỚN HƠN 200 trong các ô E4:E12.

<

=SUMIF(E4:E12,”<300″, B2:B10)

Tính tổng các giá trị trong các ô E4:E12 nếu giá trị tương ứng trong cột A NHỎ HƠN 300.

=

=SUMIF(D4:D12,E4)

Tính tổng các giá trị trong các ô D4:D12 mà có giá trị BẰNG với giá trị trong ô E4.

Tính tổng các giá trị trong các ô F4:F12 nếu giá trị tương ứng trong cột C KHÁC với giá trị trong ô I3.

Tính tổng các ô có giá trị Lớn hơn hoặc bằng 175 trong dải E4:E12

<=

=SUMIF(E4:E12,”<=150″, F4:F12)

Tính tổng các giá trị trong các ô F4:F12 nếu giá trị trong ô tương ứng trong cột E Nhỏ hơn hoặc bằng 150

Sử dụng SUMIF với các tiêu chí so sánh kiểu chuỗi

Tính tổng nếu bằng

Đối chiếu hoàn toàn: =SUMIF(C4:C12, “bananas” ,F4:F12)

Giá trị tính tổng trong các ô F4:F12 nếu ô tương ứng trong cột C chứa chính xác từ “bananas” và không dư một từ hay kí tự nào. Các ô chứa “green bananas”, “yellow bananas” không thoả điều kiện này nên không được tính.

Tính tổng nếu khác

Đối chiếu từng phần: =SUMIF(C4:C12, “*bananas*”, F4:F12)

Giá trị tính tổng trong các ô F4:F12 nếu ô tương ứng trong cột C chứa từ “bananas”, đứng độc lập hoặc đi kèm với những từ khác”. Các ô chứa “green bananas”, “yellow bananas” thoả điều kiện này nên được tính.

Giá trị tính tổng trong các ô F4:F12 nếu ô tương ứng trong cột C chứa bất kì giá trị nào mà khác “bananas”. Nếu ô chứa “bananas” có thêm 1 số từ hay kí tự khác, ví như “yellow bananas” hoặc “Green bananas” sẽ được tính tổng.

Giá trị tính tổng trong các ô F4:F12 nếu ô tương ứng trong cột C chứa bất kì giá trị nào mà khác “bananas”. Nếu ô chứa “bananas” có thêm 1 số từ khác. Các ô như “yellow bananas” hay “Green bananas” sẽ không được tính.

Cách dùng hàm SUMIFS để tính tổng có điều kiện trong Excel

Công dụng: Hàm SUMIFS là hàm tính tổng có nhiều điều kiện trong Excel

Cú pháp (syntax): =SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2,…).

Trong đó:

– sum_range: Vùng cần tính tổng.

– criteria_range1: Vùng điều kiện cần tính tổng, tham số bắt buộc.

– criteria1: Tiêu chí so sánh vùng điều kiện, là tham số bắt buộc.

– criteria_range2, criteria2: Tùy chọn các phạm vi và điều kiện bổ sung, cho phép tối đa 127 cặp criteria_range2, criteria2.

– Các ô giá trị trong vùng tính tổng sum_range chứa giá trị True được coi là 1, False được coi là 0.

– Đối số vùng điều kiện criteria_range phải có cùng kích thước với sum_range.

– Trong giá trị điều kiện criteria có thể dùng kí tự thay thế ? hoặc *.

Hãy tham khảo cách dùng hàm sumifs trong bảng sau: Tính tổng thành tiền với Tên hàng là Red Apples và có số lượng lớn hơn hoặc bằng 150.

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 “.

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.

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ổ.

Hàm SUMIF là gì ?

Trong Microsoft Excel , SUMIF là một công thức được sử dụng để cộng các giá trị lại với nhau trên một phạm vi ô. Hàm SUM cơ bản thêm các giá trị trong một phạm vi ô, bất kể giá trị là gì. Với SUMIF, người dùng có thể xác định các điều kiện cụ thể phải được đáp ứng cho các giá trị trong một phạm vi ô được đưa vào quy trình bổ sung.

Công thức và cú pháp hàm SUMIF

SUMIF( phạm vị, điều kiện, [sum_range] )

phạm vi Phạm vi của các ô mà bạn muốn áp dụng các tiêu chí chống lại. tiêu chí Các tiêu chí được sử dụng để xác định các ô cần thêm. sum_range Không bắt buộc. Đây là phạm vi của các ô để tổng hợp lại với nhau. Nếu tham số này bị bỏ qua, nó sử dụng phạm vi là sum_range .

Hàm SUMIF trả về một giá trị số.

Áp dụng đối với

Excel cho Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 cho Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Cách sử dụng hàm SUMIF

Hãy nhìn vào hình ảnh bên phải. Nếu bạn chỉ muốn thêm các giá trị lớn hơn năm, bạn có thể viết công thức SUMIF như sau:

Vì các ô A2 và A5 chứa một giá trị lớn hơn năm, chúng sẽ là các ô duy nhất được cộng với nhau bởi công thức, dẫn đến giá trị là 17.

Ví dụ về hàm SUMIF

Sử dụng hàm sumif với con số

Ví dụ 1: Hàm SUMIF bên dưới tính tổng các giá trị trong phạm vi A1: A5 nhỏ hơn hoặc bằng 10.

SUMIF(A1:A5,"<=10")

Ví dụ 2: Hàm SUMIF sau đây cho kết quả chính xác như nhau. Nó sử dụng toán tử & để nối biểu tượng ‘nhỏ hơn hoặc bằng’ và giá trị trong ô C1.

SUMIF(A1:A5,"<="&C2)

Ví dụ 3: Hàm SUMIF bên dưới (ba đối số, đối số cuối cùng là phạm vi cần tính tổng) tổng các giá trị trong phạm vi B1: B5 nếu các ô tương ứng trong phạm vi A1: A5 chứa giá trị 25.

Ví dụ 4: Hàm SUMIF sau đây cho kết quả chính xác tương tự (đối số thứ hai đề cập đến ô D1).

Sử dụng hàm sumif với chữ văn bản

Ví dụ 5: Hàm SUMIF bên dưới tính tổng các giá trị trong phạm vi B1:B5 nếu các ô tương ứng trong phạm vi A1:A5 chứa chính xác “circle”.

Ví dụ 6: Hàm SUMIF bên dưới tính tổng các giá trị trong phạm vi B1: B5 nếu các ô tương ứng trong phạm vi A1: A5 không chứa chính xác “triangle”.

Ví dụ 7: Hàm SUMIF bên dưới tính tổng các giá trị trong phạm vi B1: B5 nếu các ô tương ứng trong phạm vi A1: A5 chứa chính xác “circle” + 1 ký tự. Một dấu hỏi (?) Khớp với chính xác một ký tự.

Ví dụ 8: Hàm SUMIF bên dưới tính tổng các giá trị trong phạm vi B1: B5 nếu các ô tương ứng trong phạm vi A1: A5 chứa một chuỗi gồm 0 hoặc nhiều ký tự + le. Dấu hoa thị (*) khớp với một loạt các ký tự bằng 0 hoặc nhiều hơn.

Ví dụ 9: Các hàm SUMIF bên dưới các giá trị tổng trong phạm vi B1: B5 nếu các ô tương ứng trong phạm vi A1: A5 chứa chính xác “triangle” hoặc “circle8”.

Sử dụng hàm SUMIF thứ ngày tháng

Lưu ý: hàm DATE trong Excel chấp nhận ba đối số: năm, tháng và ngày.

Ví dụ 12: Hàm SUMIFS bên dưới (có chữ S ở cuối) tính tổng doanh số giữa hai ngày (bao gồm cả ngày bắt đầu và ngày kết thúc).

Lưu ý: hàm SUMIFS trong Excel tính tổng các giá trị dựa trên hai hoặc nhiều tiêu chí (đối số đầu tiên là phạm vi cần tính tổng, theo sau là hai hoặc nhiều cặp phạm vi / tiêu chí). Điều chỉnh ngày để tính tổng doanh số trong một tháng, năm cụ thể, v.v.