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ọc Excel 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é.

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ệ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

Xem thêm: Hướng dẫn cách nhận biết loại lỗi và cách bẫy lỗi trong Excel

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)

Cách 2: Tính trực tiếp không dùng cột phụ với 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.

Xem thêm: Hàm SUMIF / SUMIFS Tính tổng theo điều kiện

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ầ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&”*”)

Rất thú vị phải không nào. Như vậy là Học Excel 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