SUMIFS ile Koşullara Göre Toplama

SUMIF işlevinin Doğu bölgesindeki tüm satışlar gibi tek bir kuralla eşleşen değerleri topladığını zaten biliyorsunuz. Ancak gerçek sorular genellikle birden fazla koşul içerir: Doğu bölgesinin ocak ayındaki satışları ne kadardı? Burada aynı anda iki koşul vardır.

SUMIFS işte bu noktada öne çıkar. Sondaki S harfi, birçok ölçütü bir araya getirebildiğini gösterir ve bir satır yalnızca verdiğiniz tüm koşulları karşılıyorsa toplama eklenir.

Bu derste bağımsız değişkenlerin sırasını öğrenecek, ilk çok ölçütlü toplamanızı yazacak ve sık yapılan hatalardan kaçınacaksınız.

SUMIFS, SUMIF'te bekleyebileceğiniz sırayı tersine çevirir. Toplayacağınız sayılar önce gelir, ardından her koşul bir çift olarak verilir.

• sum_range — toplanacak değerler
• criteria_range1, criteria1 — ilk koşul
• criteria_range2, criteria2 — ikinci koşul

Aralık ve ölçüt çiftlerini 127 koşula kadar eklemeye devam edebilirsiniz. Aşağıdaki kalıbı sesli okuyun: bunu topla; burada bu, şuna eşit ve burada bu, şuna eşit.

A sütununda Bölge, B sütununda Ay ve C sütununda Tutar bulunan bir tablo düşünün. Doğu bölgesinin ocak ayındaki toplam satışlarını bulmak istiyorsunuz.

Toplayacağınız tutarlar C:C aralığında bulunur. İlk koşul A:A aralığında "Doğu" değerini, ikinci koşul ise B:B aralığında "Ocak" değerini arar. Bir satır yalnızca her iki koşul da doğruysa hesaba katılır.

Bu, SUMIFS ile ilgili en yaygın hatadır. Toplama aralığı ve tüm ölçüt aralıkları aynı boyutlara sahip olmalıdır — yani aynı sayıda satır ve sütun içermelidir.

Toplama aralığınız C2:C100, ancak bir ölçüt aralığınız A2:A99 ise satırlar hizalanmadığından formül #VALUE! hatası döndürür.

En güvenli yöntem, tüm aralıklar için aynı başlangıç ve bitiş satırlarını kullanmak veya A:A gibi tam sütunları tutarlı biçimde kullanmaktır.

Tırnak içinde "Doğu" değerini doğrudan yazmak işe yarar, ancak esnek bir raporda kullanıcının seçim yapabilmesi gerekir. Bölgeyi F1 hücresine, ayı da F2 hücresine yazın ve ölçütleriniz olarak bu hücrelere başvurun.

Artık F1 veya F2 değiştirildiğinde toplam hemen yeniden hesaplanır. Normal bir hücre başvurusunda tırnak işareti gerekmediğine dikkat edin — tırnak işaretleri yalnızca formülün içine yazılan gerçek metinler içindir.

Ölçütler yalnızca tam metin eşleşmeleriyle sınırlı değildir. Sayılar için karşılaştırma işleçlerini tırnak içine alarak kullanabilirsiniz.

• ">100" — 100'den büyük
• "<=50" — 50'den küçük veya 50'ye eşit
• "<>0" — sıfıra eşit değil

Burada Doğu bölgesindeki tutarları topluyor, ancak yalnızca tutarın 100'den büyük olduğu satırları hesaba katıyoruz. Toplama aralığı ile ölçüt aralığının aynı sütun olabileceğine dikkat edin.

Eşik değeri yazılmak yerine bir hücrede bulunuyorsa ne olur? ">F1" yazamazsınız; bu ifade F1 metnini olduğu gibi arar. Bunun yerine işleci hücreyle & simgesini kullanarak birleştirmeniz gerekir.

Böylece ">"&F1, F1'in içerdiği değerden büyük ölçütünü oluşturur. Bu birleştirme yöntemi, dinamik ve kullanıcıların yönlendirdiği raporlar için vazgeçilmezdir.

SUMIFS kolayca genişletilebilir. Her yeni kural için bir aralık ve ölçüt çifti daha ekleyin. D sütununda Satış Temsilcisi bulunduğunu varsayalım. Doğu bölgesinde, ocak ayında, "Maria" tarafından yapılan satışların toplamını bulabilirsiniz.

Her koşul sonucu daha da daraltır. SUMIFS AND mantığını kullandığından, bir satırın toplama dahil edilmesi için üç koşulun tümünü karşılaması gerekir.

Metin ölçütleri joker karakterleri destekler. Yıldız işareti * istediğiniz sayıda karakterle, soru işareti ? ise tam olarak bir karakterle eşleşir.

• "North*", North, Northeast ve Northwest ile eşleşir
• "*east*", içinde east geçen her şeyle eşleşir

Bu işlem "Kuzey" ile başlayan herhangi bir bölgenin tutarlarını toplar; bölge adlarınız aynı ön eki paylaşıyorsa oldukça kullanışlıdır.

Bağımsız değişkenlerin sırası ters olduğu için aradaki farkı ezberlemeye değer:

• SUMIF: range, criteria, [sum_range] — denetlenecek aralık önce gelir; sum_range isteğe bağlıdır ve sonda bulunur.
• SUMIFS: sum_range, criteria_range1, criteria1, ... — sum_range her zaman önce gelir.

İpucu: Birden fazla koşulunuz varsa doğrudan SUMIFS kullanın. Birçok kişi tek bir ölçüt için bile tutarlı tek bir kalıp kullanmak amacıyla SUMIFS'i tercih eder.

SUMIFS 0 döndürdüğünde bu genellikle formülün bozuk olduğu anlamına gelmez; tüm koşullarla eşleşen bir satır bulunmadığı anlamına gelir. Metninizde gizli boşluklar olup olmadığını, yazımın eşleşip eşleşmediğini veya bir sayının metin olarak kaydedilip kaydedilmediğini yeniden kontrol edin.

Hızlı bir tanılama yöntemi, her seferinde bir koşulu kaldırmaktır. Bir ölçütü kaldırdığınızda toplam görünüyorsa her şeyi dışarıda bırakan ölçüt odur. Bu ayırıp sınama alışkanlığı, çok ölçütlü formüllerde hata ayıklamayı hızlandırır.

Artık aynı anda birden çok koşula uyan sayıları toplayabilirsiniz:

• SUMIFS(sum_range, range1, crit1, range2, crit2, ...) — sum_range önce gelir.
• Tüm aralıklar aynı boyutta olmalıdır; aksi halde #VALUE! hatası alırsınız.
• Bir hücreyle karşılaştırma yapmak için ">"&F1, joker karakterler için * / ? kullanın.
• Koşullar AND mantığını kullanır — bir satırın tüm koşulları karşılaması gerekir.

Sırada, COUNTIFS ile birden çok koşula uyan satırları sayma var.