Cách tính tổng các mã giống nhau trong excel

Trong trường hợp bắt buộc tính tổng theo điều kiện là hầu hết ký tự giống như nhau ở vào 1 ô thì sử dụng hàm gì? Đây là thắc mắc rất thú vị nhưng mà Học Excel Online hay nhận được. Hãy cùng tìm hiểu cách làm cho trong trường vừa lòng 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, bọn họ thấy các mã ID gồm chứa những ký từ bỏ là KD1, KD2. Tuy vậy vị trí của các ký tự này sẽ không giống nhau, tức là nó có thể nằm ngẫu nhiên ở ngẫu nhiên vị trí làm sao trong ô, miễn sao có đúng cam kết tự đó.

Yêu mong là tính tổng lệch giá của những nhân viên cấp dưới có mã ID theo thứ tự là KD1 cùng KD2.

Xem nhanh

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

Cách tính tổng theo những ô bao gồm ký tự tương tự nhau vào Excel

Trong câu hỏi trên, mới đọc thì thấy hơi phức tạp. Nhưng thực ra ta có nhiều cách tính tổng vào Excel và bạn sẽ bất ngờ khi biết chúng ta có vô số cách thức 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 bạn cũng có thể dùng hàm xác định riêng biệt từng ký kết tự xem ô nào chứa ký kết tự đó. Chèn thêm cột phụ vùng phía đằng sau cột doanh thu và sử 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 bao gồm trong ô A2 xuất xắc không. Kết quả trả về nếu bao gồm sẽ là vị trí cam kết tự bắt đầu mở ra trong ô A2. Nếu không sẽ trả về lỗi #VALUEThứ 2: phối kết hợp hàm IFERROR để tránh vấn đề 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 giống như với giá chỉ trị ở ô D1 là KD2

Khi đó công dụng >0 có nghĩa là có giá bán trị đề xuất tìm, =0 là không tồn tại giá trị đề nghị tìm.

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

Kết quả tổng doanh thu của nhân viên cấp dưới có mã ID là KD1 =SUMIF(C2:C10,”>0″,B2:B10)

Tương tự nhân viên cấp dưới 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 họ phải thêm mỗi điều kiện là một trong cột, tại mỗi cột lại áp dụng nhiều công thức. Vì sao không làm cho 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 vấn đáp là hoàn toàn rất có thể làm trực tiếp được.

Xem thêm: Cách Làm Squishy Đơn Giản Nhất Tại Nhà, Сách Làm Squishy

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

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

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

Vùng điều khiếu nại là ở cột A, từ bỏ A2:A10 là ID nhân viênĐiều khiếu nại là “*”&F3&”*” tức là thêm 2 vết * ở trước và sau quý giá trong ô F3. Bởi vì dấu * là ký 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, trường đoản cú B2:B10

Kết quả cũng bằng với phương pháp thứ 1.

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

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

Các ngôn từ trong hàm này vẫn như thể hàm SUMIF, dẫu vậy thứ tự có 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: sử dụng hàm SUMPRODUCT

Hẳn bạn đã nghe nói đến hàm SUMPRODUCT rất có thể thay thế trọn vẹn cho hàm SUMIF, SUMIFS. Trong lấy ví dụ như này chúng ta sẽ coi hàm SUMPRODUCT rất có thể sử dụng được ko nhé:

Công thức tại ô I3 thực hiện 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) đó là nội dung vào cột phụ mà bọn chúng ta đã làm ở trên.

Khi đặt trong hàm SUMPRODUCT, bọn họ sẽ đối chiếu kết quả đó tất cả >0 tốt không, và với đông đảo giá trị >0 đã 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. Do đó là Học Excel Online đã giới thiệu cho mình 3 cách làm câu hỏi này. Bạn có giải pháp nào khác không? Hãy chia sẻ cùng chúng tôi nhé.

Ngoài ra chúng ta có thể tìm hiểu thêm một số nội dung bài viết cùng chủ đề:

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

Chuyên mục: Kiến thức thú vị