Hướng dẫn các cách tính tổng các mã giống nhau trong Excel có ví dụ minh họa cụ thể

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, mình 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

Cho ví dụ minh họa dưới đây là bảng gồm cột ID nhân viên, doanh thu. Theo như bảng dữ liệu, chúng ta thấy các mã ID có chứa các ký tự là KD1, KD2, tuy nhiên 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ự đó. Hãy 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ó thể thực hiện bài toán trên, đầu tiên chúng ta cần dùng hàm xác định riêng từng ký tự xem ô nào chứa ký tự đó bởi vì các ký tự nằm lẫn trong chuỗi ký tự ở mỗi ô, tiếp theo chúng ta chèn thêm cột phụ phía sau cột Doanh thu và dùng hàm SEARCH để xác định theo công thức như sau:

= IFERROR(SEARCH($C$1,A2),0)

 

Trong đó:

• Hàm search sẽ tìm kiếm giá trị trong ô C1 (là KD1) xem có trong ô A2 hay không và kết quả trả về nếu có sẽ là vị trí ký tự bắt đầu xuất hiện trong ô A2, còn nếu không sẽ trả về lỗi #VALUE.

•  

• Kết hợp hàm IFERROR với hàm SEARCH để tránh trường hợp lỗi #VALUE khi không tìm thấy bởi hàm SEARCH, khi đó giá trị lỗi sẽ được thay bằng số 0

 

sau đó chúng ta nhấn Enter và kết quả nhận được như sau:

 

 

Hình ảnh minh họa về 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

 

 

Chúng ta áp dụng tương tự với giá trị ở ô D1 là KD2, khi đó kết quả lớn hơn 0 tức là có giá trị cần tìm, bằng 0 là không có giá trị cần tìm.

 

Tiếp theo, chúng ta chỉ cần sử dụng hàm SUMIF để tính tổng doanh thu của nhân viên có mã ID là KD1 theo công thức như sau:

 

=SUMIF(C2:C10,”>0″,B2:B10)

 

sau đó chúng ta nhấn Enter và nhận được kết quả như sau:

 

 

Hình ảnh minh họa về 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

 

 

Chúng ta thực hiện tương tự nhân viên có mã KD2 theo công thức 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

Ngoài cách dùng cột phụ như phần trên thì chúng ta cũng có thể thực hiện trực tiếp bằng hàm SUMIF theo công thức với điều kiện là KD1 như sau: 

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

 

Trong đó:

• A2:A10 là vùng điều kiện là ở cột A,  là ID nhân viên

•  

• “*”&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 chúng ta cần đặt trong dấu nháy kép, sau đó sử dụng dấu & để nối ký tự.

•  

• B2:B10 là vùng tính tổng là cột Doanh thu.

 

sau đó nhấn Enter và nhận được kết quả cũng bằng với cách trên.

 

 

Hình ảnh minh họa về cách  tính tổng các mã giống nhau trong excel trực tiếp bằng hàm SUMIF

 

 

Ngoài ra nếu như chúng ta không muốn sử dụng hàm SUMIF thì chúng ta có thể thay bằng hàm SUMIFS tại H3 như sau:

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

 

Các nội dung trong hàm SUMIFS 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

Chắc hẳn khi làm việc với Excel thì chúng ta có thể thấy hàm SUMPRODUCT có thể thay thế hoàn toàn cho hàm SUMIF, SUMIFS. Trong ví dụ minh họa này chúng ta sẽ sử dụng hàm SUMPRODUCT để giải quyết ví dụ minh họa trên.

 

Chúng ta thực hiện hàm SUMPRODUCT tại ô I3 như sau:

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

 

Chúng ta 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 đã thực hiện ở phần 1. Khi đặt trong hàm SUMPRODUCT thì chúng ta sẽ so sánh kết quả đó có lớn hơn 0 hay không, và với những giá trị lớn hơn 0 thì chúng ta sẽ dùng để tính tổng trong cột Doanh thu.

 

 

Hình ảnh minh họa về cách tính tổng các mã giống nhau trong excel bằng hàm SUMPRODUCT

 

 

Tuy nhiên, nếu chúng ta chỉ thực hiện công thức với hàm SUMPRODUCT như sau thì chúng ta sẽ không nhận được 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, mình 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é.