Trong trường hợp đề nghị tính tổng theo điều khiếu nại là phần nhiều ký tự tương tự nhau ở vào 1 ô thì sử dụng hàm gì? Đây là thắc mắc rất thú vị nhưng Học Excel Online hay nhận được. Hãy cùng tò mò cách có tác dụng trong trường hòa hợp này nhé.

Bạn đang xem: Cách tính tổng các mã giống nhau trong excel

Ví dụ họ có yêu ước như sau:

*

Trong cột ID nhân viên, họ thấy những mã ID có chứa các ký từ là KD1, KD2. Dẫu vậy vị trí của những 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í làm sao trong ô, miễn sao có đúng cam kết tự đó.

Yêu mong là tính tổng lợi nhuận của những nhân viên có mã ID theo lần lượt là KD1 với KD2.


Xem nhanh


Cách tính tổng theo các ô tất cả ký tự như thể nhau trong Excel

Cách tính tổng theo những ô có ký tự tương tự nhau vào Excel

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

Cách 1: tạo nên cột phụ nhận thấy giá trị điều kiện tính

Bởi vì những ký tự ở lẫn vào chuỗi ký tự ở mỗi ô nên chúng ta có thể dùng hàm xác định riêng rẽ từng ký kết tự xem ô làm sao chứa cam kết tự đó. Chèn thêm cột phụ vùng phía đằng sau cột lợi nhuận và dùng hàm tìm kiếm xác định như sau:


*

*

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

Thứ 1: search giá trị trong ô C1 (là KD1) xem gồm trong ô A2 xuất xắc không. Tác dụng trả về nếu có sẽ là vị trí ký tự bắt đầu mở ra trong ô A2. Còn nếu không sẽ trả về lỗi #VALUEThứ 2: phối hợp hàm IFERROR để tránh sự cố lỗi #VALUE khi không kiếm thấy vì hàm SEARCH, lúc đó cực hiếm lỗi sẽ được thay thông qua số 0

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

Khi đó tác dụng >0 tức là có giá bán trị nên tìm, =0 là không tồn tại giá trị bắt buộc tìm.

Bây giờ việc trở bắt buộc khá đơn giản, bọn họ chỉ cần áp dụng hàm SUMIF để tính hiệu quả như sau:

*

Kết trái tổng doanh thu của nhân viên có mã ID là 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 sử dụng cột phụ với hàm SUMIF

Việc thêm cột phụ khiến bọn họ phải thêm mỗi điều kiện là 1 trong cột, tại mỗi cột lại thực hiện nhiều công thức. Tại sao không làm trực tiếp để tiết kiệm ngân sách và chi phí tài nguyên của Excel nhỉ? Câu trả lời là trả toàn rất có thể làm trực tiếp được.

Xem thêm: 35 Câu Thành Ngữ Tiếng Anh Về Tình Yêu Thả Thính Crush 2022, Tổng Hợp Ca Dao, Tục Ngữ Về Tình Yêu Hay Nhất

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

*

Với điều khiếu nại là KD1, bọn họ có:

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

Vùng điều khiếu nại là ở cột A, trường đoản cú A2:A10 là ID nhân viênĐiều kiện là “*”&F3&”*” tức là thêm 2 vệt * ở trước với sau quý hiếm trong ô F3. Bởi dấu * là cam kết tự đặc biệt nối với ô F3 nên cần đặt trong vệt nháy kép, sau đó thực hiện dấu & để nối ký tự.Vùng tính tổng là cột Doanh thu, từ B2:B10

Kết trái cũng bởi với giải pháp thứ 1.

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

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

Các câu chữ trong hàm này vẫn giống như hàm SUMIF, dẫu vậy thứ tự bao gồm thay đổi 1 chút khi vùng tính tổng được đưa lên thành phần thiết bị 1 ở vào hàm.

Cách 3: thực hiện hàm SUMPRODUCT

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

*

Công thức tại ô I3 áp 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ũng có thể thấy phần IFERROR(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ả đó gồm >0 tuyệt không, và với hầu như giá trị >0 đã dùng để tính tổng vào cột Doanh thu.

* Lưu ý:

Cách viết sau đây ko ra kết quả:

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

Rất thú vị nên không nào. Bởi thế là Học Excel Online đã giới thiệu cho mình 3 biện pháp làm bài toán này. Bạn có biện pháp nào khác không? Hãy share cùng cửa hàng chúng tôi nhé.

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

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