Sumproduct Array Kriter Kullanimi

Katılım
21 Ekim 2008
Mesajlar
2,326
Excel Vers. ve Dili
Office 2013 - Eng
Merhaba Arkadaslar, Tavsiyenize ihtiyacim var,

2.png

Yukaridaki gibi bir ornek tablodan ilgili hesaplari kontrol ederek baska bir tabloda toplam aldirmak istiyorum.

1.png

Yukaridaki resimde sumproduct icerisinde small kullanarak toplamlari aldigim bir ornek mevcut. Istegim ise alttaki yardimci satirlari hic kullanmadan sari bolumde tum toplami yapabilmek.

Dosyanin bir ornegini ekte paylasiyorum, Dener ve yardimci olursaniz sevinirim.
 

Ekli dosyalar

Son düzenleme:
Katılım
21 Ekim 2008
Mesajlar
2,326
Excel Vers. ve Dili
Office 2013 - Eng
Merhaba.
İstenilen sonuç aşağıdaki şekilde alınıyor sanırım.
Merhaba Ömer Bey,

Anlatmasi biraz zordu, o yuzden cok net olamadim sanirim.

"Management PL (NEW)" sayfasinin C3 hucresinde tum toplama ulasmak istiyorum.

Ne yazikki cok net anlatamiyorum, Vaktiniz olurda dosyayi incelerseniz cok sevinirim.
 

Ömer BARAN

Uzman
Katılım
8 Mart 2011
Mesajlar
13,002
Excel Vers. ve Dili
Office 2013 ( 32 bit ) TÜRKÇE
Sanırım şöyle (dizi formülüdür) ;
=SUMPRODUCT((COUNTIF(GLMapping[GL Accounts];GL_Txs[GL Account Code]))*(GL_Txs[Debit]))
Sonradan ilave not: Dizi formülü olarak uygulamadan da sonuç alınabiliyor.
FinancialsMapping sayfasına yeni hesap kodları eklendiğinde, bu kodlara ait sonuçlar da TOPLAM'a yansıyor durumda.
 
Son düzenleme:
Katılım
21 Ekim 2008
Mesajlar
2,326
Excel Vers. ve Dili
Office 2013 - Eng
3.png

Omer Bey ve Idris Bey,

Zaman ayirdiginiz ve cozum urettiginiz icin cok tesekkur ederim, Fakat duzgun anlatamadigim yahut eksik kaldigi ve zamaninizi harcadigim icin uzgunum.

Kac adet alt hesabin olacagini bilemiyorum resimdeki ornekte ilk kategoride (Hire Expenses) 4 hesap ikinci kategoride (Other Expenses) 3 hesabin toplami aliniyor.

Idris Bey'in tavsiyesindeki ust kirilima gore cozum de uretemiyorum cunku "740.01.01" ust kirilimi bir kac kategoride raporlanabiliyor.

Bu durumda ilgili hesaplari kategori isimlerine bakarak baska bir tablodan (GLMapping) kontrol etmem gerekiyor.

*Dosyayi bu mesajima tekrardan ekliyorum.
 

Ekli dosyalar

Ömer BARAN

Uzman
Katılım
8 Mart 2011
Mesajlar
13,002
Excel Vers. ve Dili
Office 2013 ( 32 bit ) TÜRKÇE
Umarım yine yanlış anlamamışımdır.
G1 hücresine uygulayın, aşağı kopyalayın.
Rich (BB code):
=SUMPRODUCT((COUNTIF(OFFSET(FinancialsMapping!$A$1;MATCH($E1;FinancialsMapping!$A:$A;0)-1;2;COUNTIF(FinancialsMapping!$A:$A;$E1);1);Txs!$B$2:$B$4602))*(GL_Txs[Debit]-GL_Txs[Credit]))

TABLO ALANLARINI YERİNE KOYUNCA:
=SUMPRODUCT((COUNTIF(OFFSET(GLMapping[[#Üstbilgiler];[Level 1 Description]];MATCH($E1;GLMapping[Level 1 Description];0);2;COUNTIF(GLMapping[Level 1 Description];$E1);1);Txs!$B$2:$B$4602))*(GL_Txs[Debit]-GL_Txs[Credit]))
 
Son düzenleme:
Katılım
21 Ekim 2008
Mesajlar
2,326
Excel Vers. ve Dili
Office 2013 - Eng
Umarım yine yanlış anlamamışımdır.
G1 hücresine uygulayın, aşağı kopyalayın.
Rich (BB code):
=SUMPRODUCT((COUNTIF(OFFSET(FinancialsMapping!$A$1;MATCH($E1;FinancialsMapping!$A:$A;0)-1;2;COUNTIF(FinancialsMapping!$A:$A;$E1);1);Txs!$B$2:$B$4602))*(GL_Txs[Debit]-GL_Txs[Credit]))

TABLO ALANLARINI YERİNE KOYUNCA:
=SUMPRODUCT((COUNTIF(OFFSET(GLMapping[[#Üstbilgiler];[Level 1 Description]];MATCH($E1;GLMapping[Level 1 Description];0);2;COUNTIF(GLMapping[Level 1 Description];$E1);1);Txs!$B$2:$B$4602))*(GL_Txs[Debit]-GL_Txs[Credit]))
Hayır yanlış anlamadınız, Ayrica formulu dahi duzenleme nezaketi gosterdiniz. Zaman harcadiginiz icin cok tesekkur ederim.

Cozume sizin gibi yaklasmak aklimin ucundan dahi gecmedi, inanin uzun donemde benim cok fazla zamanimi kurtaracaktir.
 
Katılım
21 Ekim 2008
Mesajlar
2,326
Excel Vers. ve Dili
Office 2013 - Eng
Alternatif olarak Pivot Table ile hazırladığım çözümü inceleyiniz.
Gayet guzel bir cozum olmus cok tesekkur ederim,

Problemin asil varligi calismanin ilk dizayni kendim yapmamis olmam ve suan icin mudahale edemeyecek olmam.

Formul ile bir cozum uretmem gerekiyordu ve Sumproduct icerisinde baska bir tablodan uretilen array'i dondurmeyi basaramadim. Bu durum ciddi anlamda beynimde kaşınmaya yol açtı.

Ömer Beyin cozumu ile yapmam gereken calismanin an itibari ile %90 kadarini tamamladim, Hepinize tekrardan tesekkur ederim.
 

Ömer BARAN

Uzman
Katılım
8 Mart 2011
Mesajlar
13,002
Excel Vers. ve Dili
Office 2013 ( 32 bit ) TÜRKÇE
Kalan % 10 için de her daim buralardayız.
MALTA'ya selamlar.
 

İdris SERDAR

Moderatör
Yönetici
Katılım
21 Ekim 2005
Mesajlar
17,104
Excel Vers. ve Dili
Excel, 365 - İngilizce
.

Dün tam olarak anlayamamıştım herhalde...

Soru başlığına tekrar bakınca siz sumproduct işlevinde kriter olarak tabloda yer alanları almak için bir formül istediğinizi anlamış oldum. Eğer öyleyse.;

Hire Expense için:

Kod:
=SUMPRODUCT(SUMIF(GL_Txs[GL Account Code];B2:B5;GL_Txs[Debit]))
Other Expenses in:

Kod:
=-SUMPRODUCT(SUMIF(GL_Txs[GL Account Code];B7:B9;GL_Txs[Credit]))
kullanabilirsiniz.

Dosya ekte.

.
 

Ekli dosyalar

Ömer

Moderatör
Yönetici
Katılım
18 Ağustos 2007
Mesajlar
22,196
Excel Vers. ve Dili
Microsoft 365 Tr
Ofis 2016 Tr
Merhaba,

Alternatif: (Dizi formülüdür.)

Kod:
=TOPLA(EĞER(ESAYIYSA(KAÇINCI(GL_Txs[GL Account Code]
 ;EĞER(FinancialsMapping!$A$2:$A$100=E1;FinancialsMapping!$C$2:$C$100);0))
  ;GL_Txs[Debit]-GL_Txs[Credit]))
 
Katılım
21 Ekim 2008
Mesajlar
2,326
Excel Vers. ve Dili
Office 2013 - Eng
Kalan % 10 için de her daim buralardayız.
MALTA'ya selamlar.
Merhaba Omer Bey,

Aleykum Selam, cok naziksiniz, calismami tamamladim ve sonrasi icin bir taslak dosya edindim.

.

Dün tam olarak anlayamamıştım herhalde...

Soru başlığına tekrar bakınca siz sumproduct işlevinde kriter olarak tabloda yer alanları almak için bir formül istediğinizi anlamış oldum. Eğer öyleyse.;

Hire Expense için:

Kod:
=SUMPRODUCT(SUMIF(GL_Txs[GL Account Code];B2:B5;GL_Txs[Debit]))
Other Expenses in:

Kod:
=-SUMPRODUCT(SUMIF(GL_Txs[GL Account Code];B7:B9;GL_Txs[Credit]))
kullanabilirsiniz.

Dosya ekte.

.
Merhaba Idris Bey,

Anlatamadim lutfen kusuruma bakmayin, dosyanin Omer Beylerin formulleri ile olan versiyonlarini ekledim.


Merhaba,

Alternatif: (Dizi formülüdür.)

Kod:
=TOPLA(EĞER(ESAYIYSA(KAÇINCI(GL_Txs[GL Account Code]
;EĞER(FinancialsMapping!$A$2:$A$100=E1;FinancialsMapping!$C$2:$C$100);0))
  ;GL_Txs[Debit]-GL_Txs[Credit]))
Merhaba Omer Bey,

Alternatif paylasiminiz icin cok tesekkurler, sizin formulunuz arama yapialcak tabloda siralama konusunda zorunluluk olusturmadigindan daha kompleks kullanim acisindan bana alternatif imkanlar sunacak ve formulunuzu calismamda degistiriyor olacagim.

Tekrardan hepinize cok tesekkur ederim, ne zaman alternatif bir fikre ihtiyaciniz olursa yahut benim yapabilcegim birsey olursa zaman harcamktan mutluluk duyarim., iyi calismalar...
 

Ekli dosyalar

Üst