Công thức chung
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",rng1)) + ISNUMBER(SEARCH("rat",rng1)))>0),rng2)
Tóm lược
Để tính tổng nếu các ô chứa một chuỗi văn bản hoặc chuỗi văn bản khác (nghĩa là chứa "mèo" hoặc "chuột"), bạn có thể sử dụng hàm SUMPRODUCT cùng với ISNUMBER + SEARCH hoặc FIND. Trong ví dụ được hiển thị, công thức trong ô F5 là:
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",B4:B8)) + ISNUMBER(SEARCH("rat",B4:B8)))>0),C4:C8)
trả về tổng các giá trị trong C4: C8 khi các ô trong B4: B8 chứa "mèo" hoặc "chuột".
Giải trình
Khi bạn tính tổng các ô có tiêu chí "HOẶC", bạn cần phải cẩn thận không đếm gấp đôi khi có khả năng cả hai tiêu chí đều trả về true. Trong ví dụ được hiển thị, chúng tôi muốn tính tổng các giá trị trong Cột C khi các ô trong cột B chứa "mèo" hoặc "chuột". Chúng tôi không thể sử dụng SUMIF với hai tiêu chí vì SUMIFS dựa trên logic AND. Và nếu chúng ta cố gắng sử dụng hai SUMIFS (tức là SUMIFS + SUMIFS), chúng ta sẽ đếm gấp đôi vì có những ô chứa cả "cat" và "rat".
Thay vào đó, chúng tôi sử dụng một công thức như sau:
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",B4:B8))+ISNUMBER(SEARCH("rat",B4:B8)))>0),C4:C8)
Cốt lõi của công thức này dựa trên công thức được giải thích ở đây để định vị văn bản bên trong ô có ISNUMBER và SEARCH:
ISNUMBER(SEARCH("text",range)
Khi được cung cấp một dải ô, đoạn mã này sẽ trả về một mảng các giá trị TRUE / FALSE, một giá trị cho mỗi ô trong phạm vi. Trong công thức này, chúng tôi sử dụng đoạn mã này hai lần, một lần cho "mèo" và một lần cho "chuột", vì vậy chúng ta sẽ nhận được hai mảng. Tại thời điểm này, chúng tôi có:
=SUMPRODUCT(--(((TRUE;FALSE;TRUE;FALSE;FALSE)+ (TRUE;FALSE;TRUE;TRUE;FALSE))>0),C4:C8)
Tiếp theo, chúng ta thêm các mảng này lại với nhau vì phép cộng được sử dụng trong đại số boolean cho logic OR. Phép toán tự động ép các giá trị TRUE và FALSE thành 1s và 0s, vì vậy chúng ta kết thúc với mảng bên dưới:
=SUMPRODUCT(--(((2;0;2;1;0))>0),C4:C8)
Mỗi số trong mảng này là kết quả của việc cộng các giá trị TRUE và FALSE trong hai mảng ban đầu với nhau. Trong ví dụ được hiển thị, mảng trông như thế này:
(2;0;2;1;0)
Chúng ta cần cộng những con số này lên, nhưng chúng ta không muốn đếm gấp đôi. Vì vậy, chúng ta cần đảm bảo rằng mọi giá trị lớn hơn 0 đều chỉ được tính một lần. Để làm điều đó, chúng tôi buộc tất cả các giá trị phải TRUE hoặc FALSE bằng cách kiểm tra mảng với "> 0". Điều này trả về TRUE / FALSE:
=SUMPRODUCT(--((TRUE;FALSE;TRUE;TRUE;FALSE)),C4:C8)
Sau đó, chúng tôi chuyển đổi thành 1/0 bằng cách sử dụng âm kép (-):
=SUMPRODUCT((1;0;1;1;0),C4:C8)
và cuối cùng:
=SUMPRODUCT((1;0;1;1;0),(20;15;30;20;10))
SUMPRODUCT nhân các phần tử tương ứng của hai mảng với nhau và tính tổng kết quả, trả về 70.
Tùy chọn phân biệt chữ hoa chữ thường
Hàm SEARCH bỏ qua trường hợp. Nếu bạn cần một tùy chọn nhạy cảm, hãy thay thế SEARCH bằng hàm FIND.
