Cộng Tổng Các Mã Hàng Giống Nhau Trong Excel, Cách Tính Tổng Các Mã Khác Nhau Trong Excel

Trong trường hợp phải tính tổng theođiều kiện là những ký tự giống nhauở trong 1ô thì dùng hàm gì?Đây là câu hỏi rất thú vị mà HọcExcel Online thường nhậnđược. Hãy cùng tìm hiểu cách làm trong trường hợp này nhé.

Bạn đang xem: Cộng tổng các mã hàng giống nhau trong excel

Ví dụ chúng ta có yêu cầu như sau:

Trong cột ID nhân viên, chúng ta thấy các mã ID có chứa các ký tự là KD1, KD2. Nhưng vị trí của các ký tự này không giống nhau, tức là nó có thể nằm ngẫu nhiênở bất kỳ vị trí nào trongô, miễn là cóđúng ký tựđó.

Yêu cầu là tính tổng doanh thu của những nhân viên có mã ID lần lượt là KD1 và KD2.

Cách tính tổng theo các ô có ký tự giống nhau trong Excel

Trong bài toán trên, mới đọc thì thấy khá phức tạp. Nhưng thực ra ta có rất nhiều cách tính tổng trong Excel và bạn sẽ bất ngờ khi biết chúng ta có nhiều cách giải:

Cách 1: Tạo cột phụ nhận biết giá trịđiều kiện tính

Bởi vì các ký tự nằm lẫn trong chuỗi ký tựở mỗiô nên chúng ta có thể dùng hàm xácđịnh riêng từng ký tự xemô nào chứa ký tựđó. Chèn thêm cột phụ phía sau cột Doanh thu và dùng hàm SEARCH xácđịnh như sau:

Trong câu lệnhIFERROR(SEARCH($C$1,A2),0) ta có:

Thứ 1: Search giá trị trongô C1 (là KD1) xem có trongô A2 hay không. Kết quả trả về nếu có sẽ là vị trí ký tự bắtđầu xuất hiện trongô A2. Nếu không sẽ trả về lỗi #VALUEThứ 2: Kết hợp hàmIFERRORđể tránh trường hợp lỗi #VALUE khi không tìm thấy bởi hàm SEARCH, lúcđó giá trị lỗi sẽđược thay bằng số 0

Áp dụng tương tự với giá trịởô D1 là KD2

Khiđó kết quả >0 tức là có giá trị cần tìm, =0 là không có giá trị cần tìm.

Bây giờ bài toán trở nên kháđơn giản, chúng ta chỉ cần sử dụng hàm SUMIFđể tính kết quả như sau:

Kết quả tổng doanh thu của nhân viên có mã IDlà KD1 =SUMIF(C2:C10,>0,B2:B10)

Tương tự nhân viên có mã KD2 là=SUMIF(D2:D10,>0,B2:B10)

Cách 2: Tính trực tiếp không dùng cột phụ với hàmSUMIF

Việc thêm cột phụ khiến chúng ta phải thêm mỗiđiều kiện là 1 cột, tại mỗi cột lại sử dụng nhiều công thức. Tại sao không làm trực tiếpđể tiết kiệm tài nguyên củaExcel nhỉ? Câu trả lời là hoàn toàn có thể làm trực tiếpđược.

Cách viết công thứcnhư sau:

Vớiđiều kiện là KD1, chúng ta có:

H3=SUMIF(A2:A10,*&F3&*,B2:B10)

Vùngđiều kiện làở cột A, từ A2:A10 là ID nhân viênĐiều kiện là*&F3&* có nghĩa là thêm 2 dấu *ở trước và sau giá trị trongô F3. Vì dấu * là ký tựđặc biệt nối vớiô F3 nêncầnđặt trong dấu nháy kép, sauđó sử dụng dấu &để nối ký tự.Vùng tính tổng là cột Doanh thu, từ B2:B10

Kết quả cũng bằng với cách thứ 1.

Xem thêm: Bán Bộ Bàn Ghế Gỗ Mun Hoa - Nơi Giá Rẻ, Uy Tín, Chất Lượng Nhất

Nếu không muốn sử dụng hàm SUMIF, các bạn có thể thay bằng hàm SUMIFS như sau:

H3=SUMIFS(B2:B10,A2:A10,*&F3&*)

Các nội dung trong hàm này vẫn giống hàm SUMIF, nhưng thứ tự có thayđổi 1 chút khi vùng tính tổngđượcđưa lên thành phần thứ 1ở trong hàm.

Cách 3: Sử dụng hàm SUMPRODUCT

Hẳn bạnđã nghe nói tới hàm SUMPRODUCT có thể thay thế hoàn toàn cho hàm SUMIF, SUMIFS. Trong ví dụ này chúng ta sẽ xem hàm SUMPRODUCT có thể sử dụngđược không nhé:

Công thức tạiô I3 sử dụng hàm SUMPRODUCT như sau:

=SUMPRODUCT(($B$2:$B$10)*(IFERROR(SEARCH(F3,$A$2:$A$10),0)>0))

Các bạn có thể thấy phầnIFERROR(SEARCH(F3,$A$2:$A$10),0) chính là nội dung trong cột phụ mà chúng tađã làmở trên.

Khiđặt trong hàm SUMPRODUCT, chúng ta sẽ so sánh kết quảđó có >0 hay không, và với những giá trị >0 sẽ dùngđể tính tổng trong cột Doanh thu.

* Lưuý:

Cách viết sauđây không ra kết quả:

=SUMPRODUCT(($B$2:$B$10)*($A$2:$A$10=*&F3&*)

Rất thú vị phải không nào. Như vậy là HọcExcel Onlineđã giới thiệu cho bạn 3 cách làm bài toán này. Bạn có cách nào khác không? Hãy chia sẻ cùng chúng tôi nhé.

Ngoài ra bạn có thể tham khảo thêm một số bài viết cùng chủđề:

Tại sao nên dùng hàm SUMIFS thay cho hàm SUMPRODUCT tính tổng theo nhiều điều kiện

Hướng dẫn cách dùng hàm SUMPRODUCT để đếm theo nhiều điều kiện

So sánh hàm COUNTIFS với SUMPRODUCT trong việc đếm theo nhiều điều kiện

Hàm IF nhiều điều kiện trong Excel

Nếu tiết kiệm và đầu tư bây giờ, thì sau bao năm bạn có thể nghỉ hưu

4 cách tốt nhất để xử lý khi quên mật khẩu tài liệu Excel, Word

Tuyển tập những bài viết về hàm Vlookup chi tiết, dễ hiểu

CÁCH DÙNG HÀM LEN TRONG EXCEL ĐỂ ĐẾM SỐ KÝ TỰ TRONG MỘT Ô

Cải thiện kỹ năng Excel Ngay Siêu ưu đãi mùa dịch Covid

Khóa học liên quan

Khóa học Excel từ cơ bản đến chuyên gia dành cho người đi làm Chi tiết khóa học

Khóa học Trọn bộ Power Query nền tảng cho công việc báo cáo dữ liệu Chi tiết khóa học

Hàm IF nhiều điều kiện trong Excel Hàm IF nhiều điều kiện trong Excel

Nếu tiết kiệm và đầu tư bây giờ, thì sau bao năm bạn có thể nghỉ hưu Nếu tiết kiệm và đầu tư bây giờ, thì sau bao năm bạn có thể nghỉ hưu

4 cách tốt nhất để xử lý khi quên mật khẩu tài liệu Excel, Word 4 cách tốt nhất để xử lý khi quên mật khẩu tài liệu Excel, Word

Tuyển tập những bài viết về hàm Vlookup chi tiết, dễ hiểu Tuyển tập những bài viết về hàm Vlookup chi tiết, dễ hiểu

CÁCH DÙNG HÀM LEN TRONG EXCEL ĐỂ ĐẾM SỐ KÝ TỰ TRONG MỘT Ô CÁCH DÙNG HÀM LEN TRONG EXCEL ĐỂ ĐẾM SỐ KÝ TỰ TRONG MỘT Ô

Cải thiện kỹ năng Excel Ngay Siêu ưu đãi mùa dịch Covid Cải thiện kỹ năng Excel Ngay Siêu ưu đãi mùa dịch CovidKhóa học Excel từ cơ bản đến chuyên gia dành cho người đi làmKhóa học Trọn bộ Power Query nền tảng cho công việc báo cáo dữ liệu

Đăng ký học qua Email

Hộ kinh doanh Học Excel Online.Số ĐK: 17A80048102

Khóa học Excel

Khóa học VBA

Khóa học SQL

Khóa học Google Apps Script

Khóa học Power BI

Linkedin

YouTube

Facebook

Instagram

EXCELVBAPOWER BISQLGoogle Apps ScriptGoogle SheetsPower QueryPythonPower Platform