Giới thiệu hàm SUMIF và SUMIFS
Hàm SUMIF: Dùng để tính tổng các giá trị trong một phạm vi dựa trên một điều kiện duy nhất.
= SUMIF(dải ô, tiêu chí, [dải_ô_tổng])
- dải ô: Phạm vi để kiểm tra điều kiện.
- tiêu chí: Điều kiện cần thỏa mãn.
- [dải_ô_tổng]: (Tùy chọn) Phạm vi các ô cần tính tổng. Nếu bỏ qua, dải ô sẽ được sử dụng.
Hàm SUMIFS: Dùng để tính tổng các giá trị trong một phạm vi dựa trên nhiều điều kiện.
= SUMIFS(dải_ô_tổng, dải_ô_tiêu_chí1, tiêu chí1, [dải ô_tiêu chí2, …], [tiêu chí2, …])
- dải_ô_tổng: Phạm vi các ô cần tính tổng.
- dải_ô_tiêu_chí: Phạm vi để kiểm tra điều kiện.
- tiêu chí: Điều kiện cần thỏa mãn.
SUMIF và SUMIFS cơ bản
Tiêu chí/Điều kiện cộng là Văn bản:
Ví dụ bạn muốn tính tổng số tiền của nhân viên Vũ Hoàng Gia thì chữ Vũ Hoàng Gia phải đưa trong nháy kép:
=SUMIF(C:C;“Vũ Hoàng Gia”;E:E)
Nếu tiêu chí đặt ở ô J2 chẳng hạn, thì công thức là:
=SUMIF(C:C;J2;E:E)
Tiêu chí/Điều kiện cộng là Số:
Ví dụ bạn muốn tính tổng số tiền của Tổ 1, thì số không phải đưa trong nháy kép:
=SUMIF(B:B;1;E:E)
Nếu tiêu chí đặt ở ô J3 chẳng hạn, thì công thức là:
=SUMIF(B:B;J3;E:E)
Tiêu chí/Điều kiện cộng là Ngày:
Ví dụ bạn muốn tính tổng số tiền của ngày 10/02/2024, thì ngày phải đưa trong ngoặc kép:
=SUMIF(A:A;”10/02/2024″;E:E)
Nếu tiêu chí đặt ở ô J5 chẳng hạn, thì công thức là:
=SUMIF(A:A;J5;E:E)
Lưu ý: Cách viết ngày 10/02/2024 trong công thức SUMIF phụ thuộc vào ngôn ngữ mà bạn chọn. Chẳng hạn, nếu bạn chọn Việt Nam thì là 10/02/2024, còn bạn chọn Hoa Kỳ thì là 02/10/2024
Nếu bạn quy đổi ngày 10/02/2024 sang số là 45332 (cách quy đổi xem ở đây)
=SUMIF(A:A;45332;E:E)
Nếu bạn cộng từ ngày đến ngày, thì là 2 điều kiện, nên bạn dùng SUMIF:
=SUMIFS(E:E;A:A;”>=01/02/2024″;A:A;”<=30/6/2024″)
Nếu 01/02/2024 đặt ở ô J13, và 30/06/2024 đặt ở ô J14, thì công thức trên sửa thành:
=SUMIFS(E:E;A:A;”>=”&J13;A:A;”<=”&J14)
Tiêu chí/Điều kiện cộng có dùng các toán tử:
Các toán tử được cho vào trong nháy kép.
> lớn hơn
=SUMIF(A:A;”>01/02/2024″;E:E)
hoặc
=SUMIF(A:A;”>”&J7;E:E)
>= lớn hơn hoặc bằng
=SUMIF(A:A;”>=01/02/2024″;E:E)
hoặc
=SUMIF(A:A;”>=”&J7;E:E)
< ít hơn
=SUMIF(A:A;”<01/02/2024″;E:E)
hoặc
=SUMIF(A:A;”<“&J7;E:E)
<= nhỏ hơn hoặc bằng
=SUMIF(A:A;”<=01/02/2024″;E:E)
hoặc
=SUMIF(A:A;”<=”&J7;E:E)
= bằng
=SUMIF(A:A;”=01/02/2024″;E:E)
hoặc
=SUMIF(A:A;”=”&J7;E:E)
<> không bằng
=SUMIF(A:A;”<>01/02/2024″;E:E)
hoặc
=SUMIF(A:A;”<>”&J7;E:E)
Tính toán với rỗng, ta dùng “”
=SUMIF(C:C;””;E:E)
Tính toán với không rỗng, ta dùng “<>”
=SUMIF(C:C;”<>”;E:E)
Tiêu chí/Điều kiện cộng với ký tự đặc biệt (cộng theo tiêu chí gần đúng)
Hàm SUMIF và SUMIFS sử dụng 3 ký tự: * ? và ~
Dấu sao * khớp với 0 hoặc nhiều ký tự
Cộng cả Trần Phú Thọ, Trần Quốc Hoàn, nhưng không cộng Nguyễn Trần Bảo An:
=SUMIF(C:C;”Trần*”;E:E)
Cộng cả Trần Thị Lan, Thị Nở, Quả Thị
=SUMIF(C:C;”*Thị*”;E:E)
Dấu chấm hỏi ? khớp với chính xác một ký tự, trường hợp dưới chỉ cộng Nữ, chứ không cộng Nam:
=SUMIF(D:D;”N?”;E:E)
Nếu bạn dùng N* kết quả cộng Giới cả Nam và Nữ
=SUMIF(D:D;”N?”;E:E)
Hoặc công thức dưới sẽ cộng với Phạm Thị A, Phạm Thị X, nhưng không đúng với Phạm Thị An, Phạm Thị Hoa Hồng:
=SUMIF(C:C;”Phạm Thị ?”;E:E)
Nhưng nếu bạn dùng dấu ngã ~ là một ký tự thoát cho phép bạn tìm kiếm * hoặc ?, thay vì sử dụng chúng làm ký tự đại diện, thì khi đó:
=SUMIF(C:C;”Phạm Thị ~?”;E:E)
sẽ cộng người có tên chính xác là Phạm Thị ? (ở đây ? được coi là tên, hay là ký tự thông thường, chứ không phải ký tự đại diện nữa)
Tương tự công thức dưới đây sẽ cộng Trần Phú Thọ, Trần Phú Quý:
=SUMIF(C:C;”Trần Phú *”;E:E)
nhưng
=SUMIF(C:C;”Trần Phú ~*”;E:E)
sẽ chỉ cộng người có tên chính xác là Trần Phú * (ở đây * được coi là tên, hay là ký tự thông thường, chứ không phải ký tự đại diện nữa)
SUMIF và SUMIFS với công thức mảng
Với hàm SUMIF, ta lồng hàm INDEX phía ngoài, ví dụ:
=INDEX(SUMIF(C:C;J2:J100;E:E))
Còn hàm SUMIFS ta dùng MAP, ví dụ:
=MAP(J2:J100;LAMBDA(ngay;SUMIFS(E:E;A:A;”>=”&ngay;A:A;”<=”&ngay)
SUMIF và SUMIFS nhiều điều kiện
Bạn dùng SUMIFS khi cộng nhiều điều kiện khác nhau, điều đó là hiển nhiên, nhưng bạn cũng hoàn toàn cộng theo nhiều điều kiện với hàm SUMIF, cách dùng SUMIF trong tình huống này sẽ giúp công thức của bạn trở lên gọn gàng hơn. Hãy xem giải pháp ở đây
SUMIF và SUMIFS với bảng Pivot
Với bảng Pivot, thông thường nếu dùng SUMIF ta sẽ kết hợp với INDEX và các điều kiện được ghép lại với nhau. Nếu dùng SUMIFS, ta sẽ dùng kỹ thuật MAP hai chiều ngang dọc để làm. Hoặc ta có thể thay thế bằng các hàm FILTER và QUERY (xem thêm tại đây)
SUMIF và SUMIFS để cộng dữ liệu lấy từ IMPORTRANGE
Bạn sẽ không thể sử dụng SUMIF và SUMIFS để cộng dữ liệu lấy từ IMPORTRANGE, khi đó sẽ báo lỗi:
Google Sheets có thể báo lỗi rằng “Đối số phải là 1 dải ô” (Argument must be a range) vì Google Sheets yêu cầu phạm vi trong hàm SUMIF phải là một dải ô cụ thể, trong khi dữ liệu lấy từ IMPORTRANGE được coi là một mảng, chứ không phải một dải ô chuẩn.
Hãy xem giải pháp khắc phục tại đây
SUMIF và SUMIFS nâng cao
Nội dung này thuộc Bài 18 của Khóa học, bạn có thể bỏ qua phần nâng cao này, để đến Bài 18 học sau
- Kỹ thuật cộng cơ bản với SUM, BYCOL, BYROW
- Cộng theo 1 điều kiện
- Cộng nhiều điều kiện
- Cộng nhiều điều kiện, nhiều cột (bảng Pivot)
- Cộng theo các điều kiện đã gộp ô (Merge cells )
- Cộng lũy kế từ trên xuống, từ dưới lên
- Tạo dòng tổng cộng nhiều cấp độ lên xuống theo dữ liệu
- Cộng khi dữ liệu lấy bằng IMPORTRANGE
- Cộng tổng từ nhiều sheet, nhiều file
Một số lưu ý khi dùng SUMIF và SUMIFS
Trường hợp dải ô tổng bỏ qua, tức hàm SUMIF chỉ có 2 thông số, thì khi đó Phạm vi dải ô sẽ được xác định làm dải ô tính tổng.
Ví dụ bạn muốn cộng số tiền lớn hơn 10 triệu ở tình huống trên:
=SUMIF(E:E;”>10000000″)
Nếu tiêu chí đặt ở ô J4 chẳng hạn, thì công thức là:
=SUMIF(E:E;”>”&J4)
Điều này trở nên hữu ích khi bạn muốn tính tổng của 1 cột number nhất định, ví dụ cột đó vừa có số âm, vừa có số dương, và bạn muốn tính tổng các số dương:
=SUMIF(E:E;”>0″)
Trường hợp cột Phạm vi và cột Dải ô tổng song song nhau, bạn hoàn toàn có thể viết:
=SUMIF(C2:C100;J2;E2)
thay cho việc phải viết:
=SUMIF(C2:C100;J2;E2:E100)
Các tình huống thực hành
đang làm
