Bu yazımda Excel’de bir tabloda yer alan farklı sütunlardaki verilerin içeriğine göre başka bir sütundaki verilerin süzülerek toplanması konusunu anlatacağım.
Öncelikle elimizde aşağıdaki gibi bir tablo olduğunu varsayalım. Bu tablo isteğe göre satış adetleri, ürün türü, fiyatı, toplam satış tutarı gibi farklı bilgileri de barındırabilir ancak ben daha kolay anlaşılabilmesi için basit bir tablo yaptım. Tablo aşağıya doğru kaydırılabiliyor o yüzden lütfen önce tabloyu inceleyin. Kopyalayarak Excel’e yapıştırabilirsiniz. Excel’e kopyalayacaklar için A-B-C sütunlarına kopyalama yapın, bu sayede daha sonra yazılacak formüller düzgün çalışır. Aksi takdirde ilgili hücre adreslerinin düzenlenmesi gerekecektir.
Satışçılar | Tarih | Satış Adedi |
---|---|---|
Ahmet | 1 January 2017 | 30 |
Mehmet | 1 January 2017 | 60 |
Hasan | 1 January 2017 | 25 |
Mahmut | 1 January 2017 | 40 |
Muammer | 1 January 2017 | 30 |
Ahmet | 2 January 2017 | 20 |
Hasan | 2 January 2017 | 60 |
Mahmut | 2 January 2017 | 70 |
Muammer | 2 January 2017 | 80 |
Mehmet | 2 January 2017 | 50 |
Ahmet | 3 January 2017 | 90 |
Mehmet | 3 January 2017 | 100 |
Hasan | 3 January 2017 | 110 |
Mahmut | 3 January 2017 | 130 |
Muammer | 3 January 2017 | 60 |
Mehmet | 4 January 2017 | 30 |
Hasan | 4 January 2017 | 40 |
Mahmut | 4 January 2017 | 70 |
Muammer | 4 January 2017 | 80 |
Ahmet | 4 January 2017 | 20 |
Muammer | 5 January 2017 | 45 |
Ahmet | 5 January 2017 | 15 |
Mehmet | 5 January 2017 | 20 |
Hasan | 5 January 2017 | 30 |
Mahmut | 5 January 2017 | 25 |
Şimdi bu tablodan belli bir satıcının belli tarihler arasında kaç adet satış yaptığını bulmamız gereksin. Bunun için kullanabileceğiniz farklı yöntemler mevcut. İlk formüle gelelim tabii bunun için hangi kıstaslara göre arama yaptığımı gösterecek bir tablo daha oluşturmam gerekecek ki formül içinde bu kıstasları kullanalım. (Excel’de E-F-G-H sütunlarına kopyalama yapın)
ÇokETopla Formülü İle
Tablodan hangi satıcının hangi tarihler arası ne kadar toplam satış yaptığını hesaplamak istiyorum. “Toplam Satış” sütununa yazacağım ilk formül “çoketopla” formülü. İngilizce olarak “countifs” kullanabilirsiniz. Ne demek bu formül, kısaca seçilen sütundaki verilerin, farklı sütunlarda tanımlayacağımız kıstaslara uygun olanlarını toplamakta kullanılır. Baktığınızda “etopla” (ingilizce sumif) formülü ile benzer bir mantığı olsa da farklı olarak birden fazla kıstas tanımlamamıza izin verir. Bu kadar bilgiden sonra formülümüzü yazalım. (Eğer yukarıdaki kopyalamaları belirtildiği gibi yaptıysanız H2 hücresine aşağıdaki formülü yazın)
=ÇOKETOPLA($C$2:$C$26;$A$2:$A$26;”=”&$E2;$C2:$C$26;”>=”&$F2;$C2:$C26;”<=”&$G2)
Bu formülü inceleyelim isterseniz. Aşağıda formülün bağlı olduğu hücrelerin görüntüsü mevcut

$C$2:$C$26 : toplanacak aralığı ifade eder. Satış Adedini toplamak istediğimiz için tabloda satış adedinin yer aldığı sütunu buraya giriyoruz.
$A$2:$A$26 : ilk kıstas aralığını ifade eder. İlk kıstas olarak satıcı adını alacağımızdan buraya tablonun A sütununu giriyoruz.
“=”&$E2 : Burada ilk kıstasımızı yazıyoruz. Eğer anlatılan şekilde kopyalama işlemleri yaptıysanız E2 hücresinde “Mahmut” değerinin yer alacaktır. “=”& ifadesi teknik olarak iki metni birleştirmekte kullanılır. Excel burada E2 hücresinde yer alan “Mahmut” ifadesi ile “=” ifadesini birleştirerek “=Mahmut” sonucunu elde eder. Bunun sonucunda tabloda “B2:B26” arasında içinde “Mahmut” bulunan satırları bulur. Eşittir haricinde burada kullanılabilecek farklı ifadeler var, programlamaya aşina olanlar bilir ancak bilmeyenler için kullanılabilecek tüm ifadeleri aşağıda veriyorum.
“=” : Eşittir
“<” : Küçüktür “>” : Büyüktür
“<=” : Küçük Eşit “>=” : Büyük Eşit
“<>” : Eşit Değil
Formülün bundan sonrası farklı kıstas sütunları ve bu sütunlar için farklı kıstaslar tanımlamaktan ibaret. Unutmayın önce sütun sonra o sütun için kıstası giriyoruz. Sırası ile F2 ve G2 hücrelerinde başlangıç ve bitiş tarihlerinin yer aldığını düşünürsek Excel yazdığımız formülü aşağıdaki şekilde yorumlayacaktır.
=ÇOKETOPLA($B$2:$B$26;$A$2:$A$26;”=Mahmut”;$C2:$C$26;”>=02/01/2017″;$C2:$C26;”<=04/02/2017″)
Buradaki formülü bu şekilde de yazabiliriz ancak bu şekilde yazılır ise formül çoğaltılırken metin değerleri değişmeyeceğinden her seferinde aynı kıstaslara göre arama yapılacaktır. Bu özellikle birden fazla satır içeren ve özet bilgi göstermek istediğimiz tablolarda sıkıntıya yol açacak ve elle müdahale gerektirecektir.
Bu formülde gördüğünüz gibi tablonun C2:C26 aralığı için iki farklı tarih kıstası girebiliyoruz. Bu durumda Excel bu aralıkta tarih değer 02/01/2017’den büyük ve eşit, 04/02/2017 tarihinden küçük ve eşit olan tarihleri arayacaktır.
ToplaÇarpım Formülü İle
Gelelim bu amaç için kullanılacak ikinci formüle. Bu formül Excel’in TOPLA.ÇARPIM özelliğini kullanıyor. Bu özellik kısaca seçilen farklı aralıklardaki verileri çarparak toplamak için kullanılan bir formül. Gelelim formülümüze:
=TOPLA.ÇARPIM($B$2:$B$26*($A$2:$A$26=$E2)*($C$2:$C$26>=$F2)*($C$2:$C$26<=$G2))
Bu formülün referans aldığı alanlar da aşağıda gösterilmiştir.

Bu formül ne yapıyor derseniz eğer, burada gördüğünüz gibi sıralı bir çarpma işlemi söz konusu. Öncelikle Satış adetlerinin yer aldığı B2:B26 aralığındaki verileri alıyoruz. Bunu bir dizin olarak gösterirsek (30, 60, 25, 40, 30, 20,…) sayılar dizini anlamına geliyor.
İkinci kısımda ise A2:A26 aralığını ele alıyor ve bu aralıkta E2 hücresinin sahip olduğu değere eşit olan değerleri kontrol ediyor. E2 hücresinde “Mahmut” değeri var idi. Bu durumda Excel A2:A26 aralığındaki her bir hücre değerini “Mahmut” ile kıyaslıyor ve sonucu “doğru/yanlış” veya “0/1” olarak bir dizine atıyor. Yani A2:A26 dizinindeki değerler (Ahmet, Mehmet, Hasan, Mahmut, Muammer, Ahmet,…) şekliden (Ahmet=?Mahmut, Mehmet=?Mahmut, Hasan=?Mahmut, Mahmut=?Mahmut,…) şekline buradan da sonuca göre (0,0,0,1,0,0,…) şeklinde bir dizine dönüştürülüyor.
Sonraki kısımlarda aynı kıyaslama tarih için yapılarak onlar için de bir dizin oluşturuluyor. Sonra bu üç dizinin ilk elemandan başlanarak her bir elemanı çarpılarak sonuçları toplanıyor.
VSeçTopla Formülü İle
Veri çekip toplama için göstereceğim en son yöntem Excel’in veri formüllerini içeriyor. Bunun için yapmamız gereken tabloda veri toplamada kullanılacak kıstasları tablo başlığı ve kıstas olacak şekilde alt alta yeni bir tablo şeklinde yazmamız gerekiyor. Aşağıdaki tablodaki değerleri Excel’de E5:H6 sütunlarına kopyalayın. Her formülün önünde yer alan “=” işareti önemlidir, Excel bunu göstermez ancak formülün çalışması için bu işaret kesinlikle atlanmamalıdır.
Satıcı | İlk Tarih | Son Tarih | Toplam Satış |
---|---|---|---|
=”=Mahmut” | =”>=02/01/2017″ | =”<=04/01/2017" |
Burada kullanacağımız formül de:
=VSEÇTOPLA($A$1:$C$26;$B$1;E5:G6)
Formülün referans aldığı aralıklar aşağıdaki resimde gösteriyorum.

Gördüğünüz gibi formül iki aralıktan referans almakta. Bu aralıklardan ilki “$A$1:$C$26” ana tablonun bütününü kapsar ve bir veri tabanı olarak bu tablonun kullanılmasını sağlar. Yani bu formülde kullanılacak her bir verinin hangi başlık altında hangi sütunlarda yer aldığı bilgisini bu veri tabanından almasını sağlanır. “$B$1” kısmı veri tabanındaki hangi sütunda toplama işlemi yapılacağını gösterir. “E5:G6” ise ana tablonun hangi başlığında ne tür bir kıstas kullanılacağını ifade eder. Bu tabloyu incelediğimizde ana tablodaki tüm başlıkların burada da yer aldığını görüyoruz. Eğer bir sütunda birden fazla kıstas kullanmak istiyorsak bu tabloda bu başlığı birden fazla kullanabiliriz. Tek dikkat edeceğimiz husus her iki tabloda da yer alan başlıkların bire bir aynı olmak zorunda olmasıdır. Eğer farklı yazımlar kullanıldı ya da boşluk gibi görünmeyen karakterler diğerinden farklı ise Excel hatalı sonuç verir.
Bu açıklamadan sonra kıstas tablosunu incelediğimizde satıcı sütununda “=Mahmut”, tarih sütununda ise “>=02/01/2017” ve diğer tarih sütununda “<=04/01/2017” tarihleri yer alıyor. Excel ana tablonun ilgili sütunlarını inceler ve aynı sütun başlıklarında yer alan kıstaslara uyan satırları bulur ve bu satırların Satış sütunuyla kesiştiği hücrelerde yer alan verileri toplar.
Sonuç önceki formüllerle aynıdır ancak bu formülün kullanılmasında bazı sorunlar var. Öncelikle birden fazla kişi için satış adedi bulunacak ise kıstasların yer aldığı tablo her bir kişi için ayrı ayrı aynı şekilde hazırlanmalı ve formülde bu yeni aralık kullanılmalıdır. Bu da formül çoğaltmanın bu formülde kullanılmayacağı anlamına gelir. Tabloda yer alan her bir kullanıcı için aynı tablonun el ile hazırlanması gerektiğinden zaman kaybına yol açar. Ve olası yazım hatalarının düzeltilmesi özellikle çok fazla başlık içeren tablolarda teker teker kontrol etmek zaman kaybını arttırır. Ve en kötüsü de yeni kişiler tabloya eklendiğinde tekrar bir el işçiliğine ihtiyaç duymasıdır. Bu nedenle bu formülün kullanılmasını şahsen tavsiye etmiyorum.
Evet, bu son formül ile bu konu tamamlanmış oldu. Umarım yardımcı olmuştur. Sorularınız için lütfen irtibata geçin.
Sayın hocam sadece bir tek hücredeki değişen sayıların(bir hücrede oluşturulan rastgele sayılar gibi) toplamını bir başka hücrede nasıl yapabiliriz,teşekkürler, ayrıca sitenizde çok güzel düzenlenmiş ve bilgilenmek isteyenler için çok iyi,emeğinize sağlık.
Eğer değerler tek bir hücre içinde metin olarak birbirine ekli olarak girildi ise bunu formüller ile ayırmak çok zor, çünkü tam olarak kaç tane değer var bilinmiyor. Önce bu değerleri Veri sekmesindeki Metni Sütunlara dönüştür komutu ile sütunlara çekmek sonrasında üzerlerinde işlem yapmak gerekir. Yada sağdan, soldan, uzunluk formülleri ile gerekli bölme işlemlerini yaptıktan sonra toplam alabilirsiniz. son seçenek ise Power Query
Sayın Hocam günlerdir ugrastigim bir konuda yardımınıza ihtiyacim var. Excelde bir tabloda indis ile 2 kosulu verilerde ayni degerlere sahip hucrelerin toplamini almak istiyorum. Bunu coketopla ile yapabiliyorum fakat indis ile mumkunmudur bu tesekkur ederim
merhaba,
indis ile yapabilmek için tablonun sıralı olması gerekir. aşağıdaki yazıda anlattığım gibi tabloyu alt tablolara bölerek gerekli alanda toplama işlemi yapabilirsiniz.
http://excelileharikalar.com/index.php/2018/11/13/siralanmis-ana-tabloyu-alt-tablolara-ayirma/
merhaba hocam , a sütünunda alt alta 744 adet 00:00 dan 23:00 şekilde genel olarak biçimlendirilmiş hücre var T1 zamanı 06:00 ile 17:00 arasındaki saatlerin sağındaki hücrelerde tüketim değerleri var bu değerlerin toplamını başka bir hücreye toplamasını yaptırmak istiyorum yardımcı olursanız sevinirim
merhaba,
=ÇOKETOPLA(C2:C5;B2:B5;”>=06:00″;B2:B5;”<=17:00") gibi bir formülle yapabilirsiniz.
Merhaba,
Aşağıda ki tarihleri ay olarak toplamak istiyorum. Mesela Haziran 2020 ayında kaç aracın trafik sigorta poliçesinin biteceğini görmek istiyorum. Bunu nasıl yapabilirim?
Teşekkür ederim.
düzenleme: tablo çok uzun olduğundan silinmiştir.
Merhaba,
Tarih aralığına göre verileri saymak için aşağıdaki formülü kullanabilirsiniz.
=ÇOKEĞERSAY($C$4:$C$40;”>=” & TARİHSAYISI(“01/06/2020″);$C$4:$C$40;”<=" & TARİHSAYISI("30/06/2020"))
diğelim ki bir değer var değer bir oranlara göre sürekli değişiyor. bu değişen değerin 30 tanesinin ortalmasını almak istedik nasıl alıcaz. (excelde)
Merhaba,
tam olarak ne yapmak istediğinizi anlamadım. Değişen değer nerede? bu değişen değerler nasıl değişiyor, nasıl okunuyor, nereye kaydediliyor, değişim oranları nerede? Daha açık yazabilirseniz daha iyi olur.
Saygılarımla,
merhaba bir çalışma sayfamda 4 sütun 13 bin satır veri var a sütunundaki verilerin altına a sütunun içinde olan 3 bin tane veri daha ekledik bu verileri koşullu biçimlendirme yapıp benzer olanları bulabiliyorum ama benim ihtiyacım olan aynı olanların yanına b c d sütunundaki verilerin de gelmesi nasıl yapabilirim yardımcı olursanız çok sevinirim
Merhaba,
Bu sonradan eklediğiniz veri yeni veri mi, yoksa sadece A sütununda olan verilerin bir listesi mi? Yani siz ana listenizin A sütununda yer alan verilerin benzersiz olanlarını alıp bunu alta mı eklediniz? eğer öyle ise bu sorunun cevabı bir yorumda verilemeyecek kadar uzun olacaktır. İletişim formundan iletişime geçerseniz daha iyi olur.
Saygılarımla,
13 bin satır 7 haneli sicil numalarından oluşan arşiv listemizde çalışma yapıp 3 binini imhaya ayırdık b c ve d sütununda bu dosyalara ait diğer bilgiler var onlarında 3 bin sicilin yanına getirilmesi gerekiyor sorunum tam olarak bu umarım açıklayabilmişimdir.
DÜŞEYARA formülünü kullanmayı denediniz mi? Sicil numaraları A sütununda diyelim
=DÜŞEYARA(A2;AnaTabloAaralığı;2)
gibi. Eğer işe yaramaz ise Power Query ile de yapılabilir ancak örnek dosya üzerinde çalışmak daha doğru olur.
merhaba,
bir konuyla ilgili yardımınızı rica ederim.
diyelim ki aşağıdaki gibi bir tablo var ve ben aylara göre toplam almak istiyorum. formülü ne yazmalıyım.
TARİH FİRMA TUTAR
13.03.2020 A FİRMASI 14.096,98
13.03.2020 C FİRMASI 833,38
13.04.2020 D FİRMASI 620,72
18.03.2020 G FİRMASI 2.519,93
13.04.2020 B FİRMASI 622,57
13.05.2020 H FİRMASI 9.473,60
13.07.2020 B FİRMASI 2.237,15
14.03.2020 F FİRMASI 1.894,76
14.02.2020 C FİRMASI 6.576,05
Merhaba,
Sorunuzun cevabını e-posta ile yolladım.
Saygılarımla,
merhaba aynı satırda birç firma adı var ve 1 firma 1 den fazla bulunuyor. Bunu farklı bir ekranda sadece tek cari isim o carinin toplamı, toplam fatura sayısı olarak tüm carileri tek kalemlerde getirebilir miyiz
Merhaba,
aşağıdaki sayfalardaki bilgiler faydalı olabilir:
http://excelileharikalar.com/index.php/2019/04/24/tum-eslesenleri-cekme/
http://excelileharikalar.com/index.php/2020/04/17/power-query-ile-tablolari-birlestirme/
Saygılarımla,
Merhabalar çok bilgilendirici yazılar elinize sağlık. Benim bir sorum var. Bu formulde ”>= veya >=” ölçütleri kullanırlan tarih aralığını hücreden seçememiz mümün mü?
Örneğin sütunlarda şirket adı, ödeme tarihi, ödeme miktarları yazıyor. Başka hücrelere 2 farklı tarih girerek bu hücrelerdeki tarih aralığında toplam yapmak.
Bunu denediğimde ölçütlerde tarihi el ile girersem kabul ediyor fakat ”>=G7” gibi bir ölçütü hücreden almasını isteyince kabul etmiyor. Bunu nasıl çözebiliriz?
Şimdiden teşekkürler
Merhaba,
Evet bu mümkündür. Zaten en güzel yöntem de bu tarz karşılaştırmaları hücre değeri ile yapmaktır.
Saygılarımla,
Merhaba,
Hücreden yönlendirmenin nasıl yapıldığını tarif edebilir misiniz?
Merhaba,
Eğer hücreye tıklandığında farklı bir sayfaya veya dosyaya gidilmesini kastediyorsanız hücreye sağ tıkladığınızda gelen Bağlantı ekle komutu veya Köprü işlevini kullanarak yapabilirsiniz.
Saygılarımla,
Merhabalar Hocam;
Bir sorunum var ve tam olarak çözemedim yardımcı olursanız çok mutlu olurum.
a sütunu b sütunu
2 x
2 y
şeklinde olan hücreleri tek hücrede 2,x,y şekline getirmek istiyorum.
Bunu nasıl yapabilirim yardımcı olabilir misiniz, şimdiden teşekkürler
Merhaba,
Sadece bu iki satır mı var yoksa aşağıya doğru bir seri halinde birden fazla satır var mı?
Saygılarımla,
55000 satır var 2 sütun var
Merhaba,
e-posta ile cevap verdim.
Merhabalar Hocam;
Aynı sütunda 30k satır var ve bazı hücreler tekrarlanıyor. Benim yapmaya çalıştığım ise tekrarlanan hücrelerin kalması fakat tekrarlanmayan hücrelerin silinmesi ve aynı zamanda diğer sütunların da silinen satırlara göre hücrelerinin silinmesi.
Bunun çözümü nedir? Teşekkürlerimi şimdiden iletirim 🙂
Saygılarımla
Merhaba,
Excel’de otomatik olarak satırların silinmesini sağlayacak bir yöntem yok. Ancak tekrar eden değerleri farklı bir tabloya çekmek için http://excelileharikalar.com/index.php/2019/04/24/tum-eslesenleri-cekme/ anlattığım yöntemi deneyebilirsiniz.
Saygılarımla,
Yardımlarınız için teşekkürler.
Merhaba Hocam;
Bir konuda yardımlarınıza ihtiyacım. Aynı sütünda, X, Y, Z gibi değerlerim var. En alta bunların içerisinde X ve Z leri toplamak istiyorum. Çokeğersay ile yapmak istediğimde “0” olarak veriyor, sadece eğersay ile toplamak istediğimde ise “değer” olarak hata veriyor. Ben bir yerde hata yapıyorum ama bulamıyorum. Konu ile ilgili yardımlarınızı talep etmekteyim. Saygılarımla.
Merhaba,
Bu tarz durumlarda ben kıstas olarak kullandığım değeri kontrol ederim. Bunu yapmanın en kolay yolu da (Hücre_Aralığı=Kıstas) gibi bir formül kullanarak sonrasında Formül değerlendirme ile formül sonucuna bakmak. Aralık çok büyük ise sadece kıstasa uyan aralığı girerek sonuçları kontrol ederim. eğer formül sonucu sadece “YANLIŞ” değerlerini barındırıyor ise kıstasta sorun vardır. Bu şekilde bir hata kontrolü yaparak kıstasınızı kontrol etmenizde fayda var. Eğer kıstas doğru ise ve hala formüller hata veriyor ise formül içine girilmiş kıstasları, aralıkları, yazılış biçimini ve sıralamasını kontrol edeerim.
hocam merhabalar. bir hücre içersinde hem metinler hem de sayılar var. ben bu hücre içersinde geçen sayıları toplamak istiyorum. örneğin A2 hücresinde “kelem 300 silgi 500 defter 250” gibi.. ben burda sadece sayılar toplansın istiyorum.yardımcı olursanız çok sevinirim.
Merhaba,
Öncelikle bu birleşik metin ve rakamdan rakamı ayırmanız gerek. Bunun için:
=TOPLA.ÇARPIM(–SAĞDAN(A1:A10;UZUNLUK(A1:A10)-BUL(” “;A1:A10)))
Gibi bir formül kullanabilirsiniz.
Veya VERİ sekmesinde METNİ SÜTUNLARA DÖNÜŞTÜR komutu ile önce boşluklara göre metin ile rakamları ayırıp 2 ayrı sütun elde edersiniz sonrasında bu sütundaki değerleri topyalabilirsiniz.
Ya da yukarıdaki işlemi –SAĞDAN(A1;UZUNLUK(A1)-BUL(” “;A”) formülü ile metnin rakam kısmını B sütununa alarak bu sütunu toplayabilirsiniz.
Saygılarımla,