Tüm Excel çalışma kitabı içinde belirli metnin oluşumlarını sayma

COUNTIF işleviyle bir hücre aralığında belirli bir metnin veya sayısal değerin kaç kez geçtiğini saymak oldukça 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ç duyulur. Bu makalede, bu soruna çözüm sunmak amacıyla SUMPRODUCT, COUNTIF ve INDIRECT işlevlerine dayalı bir formül açıklanmaktadır.

Tüm çalışma kitabındaki oluşumlar nasıl sayılır?

Çalışma kitabınızda Sheet2, Sheet3, Sheet4 ve Sheet5 olmak üzere dört çalışma sayfası olduğunu ve bu dört sayfa boyunca “Laptop” metninin kaç kez geçtiğini saymak istediğinizi varsayalım. Bunu aşağıdaki gibi kolayca gerçekleştirebilirsiniz.

Genel Formül

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

Bağımsız Değişkenler

Bu formülü nasıl kullanırsınız?

1. Öncelikle yeni bir sayfa oluşturmalı ve tüm sayfa adlarını bir hücre aralığına listelemelisiniz. Bu örnekte, sayfa adlarını B7:B10 aralığına listeledim.

2. Sonucu görüntülemek için boş bir hücre seçin.

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

Belirli metni zaten bir hücreye (örneğin B3) girdiyseniz, formülde bu hücreye aşağıdaki gibi başvurabilirsiniz:

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

Ya da belirli metni formülde çift tırnak işaretleri arasına alarak doğrudan yazabilirsiniz:

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

Not: Buradaki A1:Z10000 aralığı örnek amaçlıdır; verilerinize uygun şekilde özelleştirebilirsiniz.

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

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

• "'"&B7:B10&"'!A1:Z10000": Burada, dizi içindeki her sayfa adı A1:Z10000 aralığıyla birleştirilerek {"'Sheet2'!A1:Z10000";"'Sheet3'!A1:Z10000";"'Sheet4'!A1:Z10000";"'Sheet5'!A1:Z10000"} biçiminde bir dizi oluşturulur.
• INDIRECT({"'Sheet2'!A1:Z10000";"'Sheet3'!A1:Z10000";"'Sheet4'!A1:Z10000";"'Sheet5'!A1:Z10000"}): INDIRECT işlevi, dört farklı sayfadaki aralıklara başvurur.
• COUNTIF(INDIRECT("'"&B7:B10&"'!A1:Z10000"),B3): COUNTIF işlevi, belirtilen değerin her sayfada kaç kez geçtiğini sayar ve {13;13;13;13} şeklinde bir dizi döndürür. Bu da ilgili metnin her sayfada 13 kez geçtiği anlamına gelir.
• SUMPRODUCT({13;13;13;13}): SUMPRODUCT işlevi, dizideki tüm sayıları toplar ve nihai sonucu 52 olarak verir.

İlgili işlevler

İlgili formüller