Hướng dẫn các cách tính tổng các mã giống nhau trong Excel

Excel là một trong những phần mềm vô cùng hữu ích để cộng giá trị thỏa mãn một hay nhiều điều kiện là hàm SUMIF và SUMIFS. Tuy nhiên, trong trường hợp nếu như chúng ta cần tìm tổng các giá trị có các ô có một đoạn mã giống nhau thì sẽ thực hiện như thế nào và cần sử dụng công thức gì?
Trong bài viết sau đây, studyexcel sẽ chia sẻ về các cách tính tổng các mã giống nhau trong Excel một cách chi tiết, cụ thể và kèm theo ví dụ minh họa để các bạn có thể dễ dàng thực hiện thao tác hơn.

1. Cách tính tổng các mã giống nhau trong Excel bằng cách tạo cột phụ và hàm SUMIF

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 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ệnh IFERROR(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 #VALUE
Thứ 2: Kết hợp hàm IFERROR để 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ã 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)

2. Cách tính tổng các mã giống nhau trong excel trực tiếp bằng hàm SUMIF

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ủa Excel 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ứ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 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ên cầ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.

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.

3. Cách tính tổng các mã giống nhau trong excel bằ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ầ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ả đó 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&”*”)

Qua bài viết chia sẻ của mình về các cách tính tổng các mã giống nhau trong Excel, studyexcel hy vọng các bạn có thể nắm rõ và có thể ứng dụng để xử lý công việc của mình. Tuy nhiên mỗi cách tính tổng các mã giống nhau trong Excel trên sẽ có những ưu điểm và hạn chế riêng nên tùy vào yêu cầu từng bài để có thể áp dụng nhé.