Tüm Excel çalışma kitabında belirli metinlerin kaç kez geçtiğini sayma

COUNTIF fonksiyonu ile bir hücre aralığında belirli bir metin veya sayı değerinin kaç kez geçtiğini saymak kolaydır. Ancak, tüm çalışma kitabında belirli bir metnin kaç kez geçtiğini saymak için daha karmaşık bir formüle ihtiyaç vardır. Bu makale, bu sorunu çözmek için SUMPRODUCT, COUNTIF ve INDIRECT fonksiyonlarına dayalı bir formülü ele alacaktır.

Tüm çalışma kitabında geçişleri nasıl sayılır?

Diyelim ki çalışma kitabınızda 4 çalışma sayfası (Sheet2, Sheet3, Sheet4 ve Sheet5) var ve bu 4 çalışma sayfasında “Laptop” metninin kaç kez geçtiğini saymak istiyorsunuz. Bunu şu şekilde yapabilirsiniz:

Genel Formül

=SUMPRODUCT(COUNTIF(INDIRECT(“‘”&range1&”‘!”&range2),criteria))

Argümanlar

Bu formülü nasıl kullanılır?

1. Öncelikle yeni bir sayfa oluşturmanız ve tüm sayfa adlarını bir hücre aralığında listelemeniz gerekecek. Bu durumda, tüm sayfa adlarını B7:B10 aralığında listeliyorum.

2. Sonucu çıkarmak için boş bir hücre seçin.

3. Aşağıdaki formüllerden birini girin ve sonucu almak için Enter tuşuna basın.

Eğer belirli metni zaten bir hücreye girdiyseniz (örneğin B3), bu hücreyi formülde şu şekilde referans alabilirsiniz:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&B7:B10&"'!A1:Z10000"),B3))

Veya doğrudan formülde çift tırnak içine alınmış belirli metni şu şekilde yazabilirsiniz:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&B7:B10&"'!A1:Z10000"),"Laptop"))

Not: Buradaki A1:Z10000 aralığı rastgele bir aralıktır, bunu verilerinize uygun şekilde değiştirebilirsiniz.

Bu formül nasıl çalışır?

=SUMPRODUCT(COUNTIF(INDIRECT("'"&B7:B10&"'!A1:Z10000"),B3))

• "'"&B7:B10&"'!A1:Z10000": Burada dizideki her sayfa adı, A1:Z10000 aralığına birleştirilir ve {"'Sheet2'!A1:Z10000";"'Sheet3'!A1:Z10000";"'Sheet4'!A1:Z10000";"'Sheet5'!A1:Z10000"} şeklinde bir dizi elde edersiniz.
• INDIRECT({"'Sheet2'!A1:Z10000";"'Sheet3'!A1:Z10000";"'Sheet4'!A1:Z10000";"'Sheet5'!A1:Z10000"}): INDIRECT fonksiyonu, dört sayfadaki aralıklara atıfta bulunur.
• COUNTIF(INDIRECT("'"&B7:B10&"'!A1:Z10000"),B3): COUNTIF fonksiyonu, belirli değerin her sayfada kaç kez geçtiğini sayar ve {13;13;13;13} şeklinde bir dizi döndürür. Bu, belirli metnin her sayfada 13 kez geçtiği anlamına gelir.
• SUMPRODUCT({13;13;13;13}): SUMPRODUCT fonksiyonu, dizideki tüm sayıları toplar ve nihai sonucu 52 olarak döndürür.

İlgili fonksiyonlar

İlgili formüller