Genel formül
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",rng1)) + ISNUMBER(SEARCH("rat",rng1)))>0),rng2)
Özet
Hücreler bir metin dizesi veya başka bir metin dizesi içeriyorsa (yani "kedi" veya "sıçan" içeriyorsa) toplamak için SUMPRODUCT işlevini ISNUMBER + SEARCH veya FIND ile birlikte kullanabilirsiniz. Gösterilen örnekte, F5 hücresindeki formül şöyledir:
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",B4:B8)) + ISNUMBER(SEARCH("rat",B4:B8)))>0),C4:C8)
B4: B8'deki hücreler "kedi" veya "sıçan" içerdiğinde, C4: C8'deki değerlerin toplamını döndürür.
Açıklama
Hücreleri "VEYA" ölçütleriyle topladığınızda, her iki ölçütün de doğru dönme olasılığı olduğunda iki kez saymamaya dikkat etmeniz gerekir. Gösterilen örnekte, B sütunundaki hücreler "kedi" veya "sıçan" içerdiğinde Sütun C'deki değerleri toplamak istiyoruz. ETOPLA'yı iki ölçütle kullanamayız, çünkü ÇOKETOPLA, VE mantığına dayalıdır. Ve eğer iki SUMIFS (yani SUMIFS + SUMIFS) kullanmaya çalışırsak, sayımı iki katına çıkaracağız çünkü hem "kedi" hem de "sıçan" içeren hücreler var.
Bunun yerine, şöyle bir formül kullanıyoruz:
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",B4:B8))+ISNUMBER(SEARCH("rat",B4:B8)))>0),C4:C8)
Bu formülün özü, burada açıklanan ve ISNUMBER ve ARA ile bir hücrenin içindeki metni bulan formüle dayanmaktadır:
ISNUMBER(SEARCH("text",range)
Bir hücre aralığı verildiğinde, bu pasaj, aralıktaki her hücre için bir değer olan bir DOĞRU / YANLIŞ değerler dizisi döndürür. Bu formülde, bu parçacığı iki kez, bir kez "kedi" ve bir kez "sıçan" için kullanıyoruz, böylece iki dizi elde edeceğiz. Bu noktada elimizde:
=SUMPRODUCT(--(((TRUE;FALSE;TRUE;FALSE;FALSE)+ (TRUE;FALSE;TRUE;TRUE;FALSE))>0),C4:C8)
Sonra, bu dizileri topladık çünkü toplama, OR mantığı için boole cebirinde kullanılıyor. Matematik işlemi, DOĞRU ve YANLIŞ değerlerini otomatik olarak 1'ler ve 0'lara zorlar, bu nedenle aşağıdaki diziyi elde ederiz:
=SUMPRODUCT(--(((2;0;2;1;0))>0),C4:C8)
Bu dizideki her sayı, orijinal iki dizideki DOĞRU ve YANLIŞ değerlerinin birlikte eklenmesinin sonucudur. Gösterilen örnekte dizi şuna benzer:
(2;0;2;1;0)
Bu sayıları toplamamız gerekiyor, ancak iki kez saymak istemiyoruz. Bu nedenle, sıfırdan büyük herhangi bir değerin sadece bir kez sayıldığından emin olmalıyız. Bunu yapmak için, diziyi "> 0" ile kontrol ederek tüm değerleri TRUE veya FALSE olarak zorlarız. Bu, DOĞRU / YANLIŞ döndürür:
=SUMPRODUCT(--((TRUE;FALSE;TRUE;TRUE;FALSE)),C4:C8)
Daha sonra çift negatif (-) kullanarak 1 / 0'a dönüştürürüz:
=SUMPRODUCT((1;0;1;1;0),C4:C8)
ve sonunda:
=SUMPRODUCT((1;0;1;1;0),(20;15;30;20;10))
SUMPRODUCT, iki dizinin karşılık gelen öğelerini birlikte çarpar ve sonucu toplayarak 70 döndürür.
Büyük / küçük harfe duyarlı seçenek
SEARCH işlevi büyük / küçük harf kullanımını dikkate almaz. Hassas bir seçeneğe ihtiyacınız varsa, ARAMA'yı BUL işleviyle değiştirin.
