Hàm SUMIF: Có thể nào kết hợp nhiều điều kiện vào Criteria?

[Quỳnh]

Ví du:̣
Em có 1 bảng tính gồm các cột:
Cột A là Tên mặt hàng
Cột B là Tên người bán
Cột C là Số tiền phải thanh toán cho người bán.

Thì công thức để tính tổng số tiền phải thanh toán cho người bán X là:

=SUMIF(B2:B100,"X",C2:C100)

Nhưng nếu muốn tính tổng số tiền phải thanh toán cho người bán X về mặt hàng A, thì phải làm thế nào?

 

[handung107]

Bạn phải dùng công thức mảng. Vấn đề này đã được thảo luận nhiều.
http://www.unifiedtech.net.au/webketoan/forum/showthread.php?t=6797
http://www.unifiedtech.net.au/webketoan/forum/showthread.php?t=5375
http://www.unifiedtech.net.au/webketoan/forum/showthread.php?p=32110
Và còn rải rác trên diễn đàn rất nhiều, bạn theo dõi xem

 

[levanduyet]

Bạn phải dùng công thức mảng. Vấn đề này đã được thảo luận nhiều.
http://www.unifiedtech.net.au/webketoan/forum/showthread.php?t=6797
http://www.unifiedtech.net.au/webketoan/forum/showthread.php?t=5375
http://www.unifiedtech.net.au/webketoan/forum/showthread.php?p=32110
Và còn rải rác trên diễn đàn rất nhiều, bạn theo dõi xem

Chị handung107 và các bạn nhỉ !
Làm sao mình tổ họp lại box EFC để có thể tra tài liệu dễ dàng nhỉ? Hiện tại nhiều khi mình chỉ vào trang đầu tiên của EFC mà thôi!?
Lê Văn Duyệt

 

[vinhnl]

Bạn dùng công thức mảng như sau:

=SUM(IF("A"&"X"=A2:A100&B2:B100,C2:C100)) và nhấn tổ hợp phím CTRL+SHIFT+ENTER

Khi bạn quay lại ô vừa điền công thức bạn sẽ thấy:

{=SUM(IF("A"&"X"=A2:A100&B2:B100,C2:C100))}

Có thể giải thích công thức trên như sau:

Cộng bên cột C2:C100 các Ci thỏa mãn tổ hợp Ci & Bi = "A" & "X"

 

[Quỳnh]

Cảm ơn chị HanDung, em đã đọc theo đường chỉ dẫn của chị và làm được rồi:

{=SUM((A2:A100="A")*(B2:B100="X")*C2:C100)}
Hoặc dùng:
{=SUMPRODUCT((A2:A100="A")*(B2:B100="X")*C2:C100)}

Bạn vinhnl: Tôi đã làm thử theo cách của bạn nhưng không hiểu sao không được?

Gửi các bác: Em cũng mong như bác Levanduyet nói, làm sao để có thể tra cứu tài liệu một cách nhanh nhất. Bởi nhiều lúc như em mới tham gia cũng chưa có thời gian để đọc hết các thảo luận, nên không biết chúng ở đâu. Hoặc có khi đọc mà không... để ý. Hìhìhì.

 

[handung107]

Công thức mảng như bạn vinhnl giới thiệu cho quỳnh là đún gđấy, không hiểu sao bạn làm không được ? Bạn xem lại nhé.
Bạn có thể đặt tên cho mảng đó, cho công thức dễ nhìn hơn. TD : A2:A100 là TenMH, B2:B100 là TenNB, C2:C100 là SoTien.

Công thức mảng có thể viết gọn lại thành :
1/ {SUM((TenMH="A")*(TenNB="X")*SoTien)}
2/SUMPRODUCT((TenMH="A")*(TenNB="X")*SoTien)
3/{SUM((TenMH&TenNB="A"&"X")*SoTien)}
4/{SUM(IF(TenMH&TenNB="A"&"X",SoTien))}
Cặp dấu {}, bạn không tự ý nhập mà chỉ nhập những gì bên trong cặp dấu này, rồi nhấn Ctrl+shift+Enter, Excel sẽ hiểu đây là công thức mảng và tự động điền cặp dấu này vào cho bạn

 

[Quỳnh]

Ơ, đúng rồi. Em vừa thử làm lại công thức như bạn vinhnl hướng dẫn thì được rồi ạ. Cảm ơn các bác!
Như vậy là có 4 cách để tính tổng theo nhiều điều kiện.

 

[donjuan_acc]

Tra loi bai cua Quynh ve sumif ket hop nhieu criteria

Su dung cong thuc mang rat hay, o day, toi bo sung them 1 cach don gian nay:
Ban tao them 1 cot moi (sau do hide) ket hop 2 cot "mat hang-nguoi ban", roi dung cot nay lam dieu kien cho ham sumif.

Vi du:
Cot A- mat hang : X, Y, Z.
Cot B- nguoi ban : A, B, C.
Toi tao them:
Cot C- ket hop : X-A, Y-B, Z-C.
Luc do :
Cot D toi dung sumif voi dieu kien la cot C.

Vai y nho goi den ban, chuc ban va moi nguoi toi CN vui ve!

 

[ForestC]

Phong cách công thức mảng của chị Dung rất hay, tuy nhiên MaiKa vẫn chưa lĩnh hội hết được. Quỳnh bảo rằng có 4 cách thực hiện cái việc "ấy" của Quỳnh, nhưng đó mới chỉ là 4 cách chị biết thôi nhé. MaiKa thì lại hay dùng công thức mảng theo phong cách sau (lấy theo đề bài của Quỳnh luôn nhé?!):

=sum(if($a$2:$a$100="A";if($b$2:$b$100="X";$c$2:$c$100;0);0)

P/S: Trong Excel càng ít phải tạo thêm cột càng tốt, vì cái đó chính là sự sử dụng linh hoạt các hàm. Còn nếu trong những trường hợp bất khả kháng thì mới nên tạo thêm.

 

[Quỳnh]

Chào Donjuan acc: Bắt tay cái nào, vì có cùng ý tưởng "nhớn" giống Quỳnh (tạo thêm cột). Tôi rất (rất rất) hay phải tính tổng theo nhiều điều kiện. Có rất nhiều việc phải dùng đến nó, nhất là khi chúng tôi thí điểm chương trình kế toán mới, thường xuyên phải kiểm tra xem số liệu tổng hợp ra biểu báo cáo có đúng không. Ngoài ra, còn nhiều nhu cầu khác nữa, mà phần mềm kế toán chưa đáp ứng được.
Tôi cũng thường tạo thêm 1 cột kết hợp nhiều điều kiện. Hìhìhì, nhưng mà vẫn thấy không ổn, tôi vẫn muốn làm theo cách "chính thống" cơ. Thế nên mới hỏi thăm ở khắp mọi nơi.

Hihihi, cảm ơn Maika. Đây đúng là cách thứ 5 rồi. Cách này có vẻ dài hơn của chị Handung107, nhưng nó đúng là cách thứ 5.

 

[handung107]

Công thức mảng của MaiKa không sai, nhưng nên sử dụng công thức mảng theo các dạng trên, vì nếu kết hợp nhiều điều kiện OR, thì công thúc mảng của MaiKa sẽ không thể hiện được. Dạng công thức mảng dễ sử dụng nhất vẫn là :
{SUM((TenMH="A")*(TenNB="X")*SoTien)}
Hay :
SUMPRODUCT((TenMH="A")*(TenNB="X")*SoTien)
Trong trường hợp điều kiện là OR (tính tổng theo TenMH là A hay TenNB là "X"), công thức trên sẽ thay đổi dấu * thành dấu + :
{SUM(((TenMH="A")+(TenNB="X"))*SoTien)}
Hay :
SUMPRODUCT(((TenMH="A")+(TenNB="X"))*SoTien)

 

[vutienduc]

Dùng mảng sẽ tốn nhiều bộ nhớ lắm, nên dùng sumproduct hay hơn

 

[SA_DQ]

Dùng Hàm DSum(...;...;......) cũng sẽ được, thật đấy!
Cụ thể hơn như sau:
=Dsum(CSDL,Chuan,KQua)
CSDL: Các cột chứa CSDL (được đặt tên CSDL)
Chuan: Giống như trường hợp trích lọc dữ liệu
KQua: Ô chứa kết quả số liệu

Thử đi, Sẽ dễ hơn chúng ta tưởng nhiều. :0frown:

 

[workman]

Dùng Hàm DSum(...;...;......) cũng sẽ được, thật đấy!
Cụ thể hơn như sau:
=Dsum(CSDL,Chuan,KQua)
CSDL: Các cột chứa CSDL (được đặt tên CSDL)
Chuan: Giống như trường hợp trích lọc dữ liệu
KQua: Ô chứa kết quả số liệu

Thử đi, Sẽ dễ hơn chúng ta tưởng nhiều. :0frown:

Đúng quá. DSum hoặc trong trường hợp này là tiện nhất và cover được nhiều điều kiện.

 

[SA_DQ]

Cảm ơn WM quá khen!

Nếu thích các bạn tím hiểu hết các hàm của nó cũng hay, Chúc tốt lành sẽ đến với mọi người! :dzo: :atom:

 

[new AQ]

Sorry New AQ nhầm. Nhwng mà cũng có thể dùng hàm concatenate để tạo ra 1 đối số dùng cho Criteria được

 

[workman]

Bạn WorkMan quá khen, vì mình thấy ít ai nói đến các hàm CSDL quá nên chen vào chút vậy mà; Nếu thích các bạn tím hiểu hết các hàm của nó cũng rất cần, Chúc tốt lành sẽ đến với mọi người! :dzo: :atom:

Thực ra đó là hàm mảng, không phải CSDL. Ờ mà thực ra cũng tương tự, gọi là "tiểu" CSDL cũng được.

 

[metwametwa]

=SUM(IF(INDIRECT($H$2)=$C13,1,0)*IF(INDIRECT($G$2)>=$J$7,1,0)*IF(INDIRECT($G$2)<=$L$7,1,0)*INDIRECT($J$2))
Trời ơi, em mới biết trang web này và thay hay hay ghe, em dot Excel lam, Có Bác nào nhiều kinh nghiệm chỉ cho em hàm này có y nghĩa gì được k a? Cám ơn nhiều nhiều!

 

[workman]

=SUM(IF(INDIRECT($H$2)=$C13,1,0)*IF(INDIRECT($G$2)>=$J$7,1,0)*IF(INDIRECT($G$2)<=$L$7,1,0)*INDIRECT($J$2))
Trời ơi, em mới biết trang web này và thay hay hay ghe, em dot Excel lam, Có Bác nào nhiều kinh nghiệm chỉ cho em hàm này có y nghĩa gì được k a? Cám ơn nhiều nhiều!

Công thức bạn nêu không rõ ràng nên chỉ có thể bàn loạn sơ sơ thế này thôi:

Bạn đã biết hàm SUM và IF rồi. Tôi chỉ có một vài comments về cách sử dụng chúng thôi.
- Hàm SUM: Tôi không thấy vai trò hàm SUM ở đây để làm gì cả. Bạn bỏ luôn hàm SUM thì về nguyên tắc kết quả vẫn như vậy thôi.
- Hàm IF: về nguyên tắc cũng không cần trong công thức này. Bạn có thể bỏ luôn hàm IF cho đỡ rườm rà. Công thức giản lược sẽ trông như sau

=(INDIRECT($H$2)=$C13)*(INDIRECT($G$2)>=$J$7)*(INDIRECT($G$2)<=$L$7)*INDIRECT($J$2)

- Hàm INDIRECT: chắc là bạn cũng biết rồi. Nhắc lại một chút thôi: đây là hàm tham chiếu từ 1 ô sang 1 ô khác. VD: Tại ô H2 tôi gõ A13, thì hàm INDIRECT(H2) của bạn ở trên sẽ tham chiếu đến giá trị của ô A13.

Bây giờ tôi diễn nôm toàn bộ công thức nhé: Ô bạn đang xét sẽ có giá trị bằng với tham chiếu của ô J2 nếu thỏa tất cả các điều kiện:
a/ INDIRECT($H$2)=$C13
b/ INDIRECT($G$2)>=$J$7 và
c/ INDIRECT($G$2)<=$L$7

bất kỳ điều kiện nào ở trên không thỏa mãn, giá trị sẽ bằng 0.

(nghe diễn nôm còn rùng rợn khó hiểu hơn tự đọc công thức )

Thế nhé. Chúc bạn vui.

 

[bethuong]

Cac ban oi!giúp mình làm hàm sum ì với nhé