Pak Anton, Pakai SumProduct saja ya... OpenOffice punya fungsi SumProduct juga kok. Bahkan lebih bagus dari Excel.
Formula Excel : =countifs($T$5:$T$283;T392;$O$5:$O$283;">1")-countifs($T$5:$T$283;T392;$O$5:$O$283;">7") bahasa manusia : "Hitung jumlah cacah dari records yang kolom T nya adalah senilai T392 dan kolom O nya lebih dari 1 tapi tidak lebih dari 7" Diubah menjadi memanfaatkan fungsi SumProduct : =SumProduct( ($T$5:$T$283=T392) * ($O$5:$O$283>1) * ($O$5:$O$283<7) ) Jika di OpenOffice, formula SumProduct di atas juga bisa ditulis dengan : (regional setting Indonesian) =SumProduct( $T$5:$T$283=T392 ; $O$5:$O$283>1 ; $O$5:$O$283<7 ) Formula CountIFs di Excel yang di atas sana bisa disederhanakan menjadi : =countifs($T$5:$T$283;T392;$O$5:$O$283;">1";$O$5:$O$283;"<7") ;) Wassalam, Kid. On Mon, May 19, 2014 at 10:46 AM, 'Fendik Anton Wijaya' fendik.an...@tafinance.com [belajar-excel] <belajar-excel@yahoogroups.com>wrote: > > > Dear Mr Kid Yth, > > Ternyata rumus saya yg dibawah2nya berimbas smuany...error smua...jadi > rusak smua kerjaan saya...jadi minta bantuan skali lagi Pak yha... > > Klo saya pake rumus > =countifs($T$5:$T$283;T392;$O$5:$O$283;">1")-countifs($T$5:$T$283;T392;$O$5:$O$283;">7") > –> ni muncul #name? lagi... > > Apa rumus countifs yg begitu dasar g compatible jg Pak di open > office?...makin bingung saya pake open office *garuk2 kepala lagi*... > > best regards, > terima kasih banyak... > Anton... > > *From:* mailto:belajar-excel@yahoogroups.com<belajar-excel@yahoogroups.com> > *Sent:* Monday, May 19, 2014 11:30 AM > *To:* belajar-excel@yahoogroups.com > *Subject:* Re: [belajar-excel] Apply rumus dari excel ke open office kok > error > > > > Dear Mr Kid Yth, > > Pak anda benar2 luar biasa...amal anda banyak Pak...masalah saya sdh > done... > > Klo anda main ke banjarmasin/kalsel trus pengen dijamu atau diajak > keliling email saya Pak...saya siap bantu... > > Kita bisa g Pak add dokumentasi rumus ke open office klo misal ternyata g > direcord di open office?...trus gimana y Pak taunya rumus yg direcord di > openoffice?... > Biar kedepannya saya tambahin sendiri klo ada masalah yg sama... > > Saya benar2 terima kasih banyak Pak yha...banyak membantu orang dlm hal > excel... > Klo ngeluarin buku ato apa tentang excel promoin saya Pak... > > best regards, > Thanks... > Anton... > > *From:* mailto:belajar-excel@yahoogroups.com<belajar-excel@yahoogroups.com> > *Sent:* Monday, May 19, 2014 11:17 AM > *To:* BeExcel <belajar-excel@yahoogroups.com> > *Subject:* Re: [belajar-excel] Apply rumus dari excel ke open office kok > error > > > > Pak Anton, > > formula : > =IF(TODAY()>B4;DATEDIF(B4;TODAY();”d”)+1;0) > untuk menghitung selisih hari kan ya... > Bagian yang berbunyi > DATEDIF(B4;TODAY();”d”) > bisa diganti menjadi : > Today()-b4 > > Formula menjadi : > =IF(TODAY()>B4;TODAY-b4+1;0) > > Fungsi DateDif tidak ada dalam openoffice. Fungsi ini juga tidak > didokumentasikan dalam Excel (entah apa penyebabnya). > > Wassalam, > Kid. > > > > > > > > > > > On Mon, May 19, 2014 at 9:23 AM, 'Fendik Anton Wijaya' > fendik.an...@tafinance.com [belajar-excel] > <belajar-excel@yahoogroups.com>wrote: > >> >> Dear Yth Mr Kid, >> >> Rumus sebenarnya ni Pak >> >> =IF(TODAY()>B4;DATEDIF(B4;TODAY();”d”)+1;0) >> >> Utk yg cell F4 tu dah bener kluar angka 0 soalny emang g overdue... >> Tpi cell yg F5 tu hasilnya keluar #name?...klo ada yg overdue hasil yg >> kluar #name? lagi... >> >> Dan anehnya Pak...klo saya kluar & save trus saya buka lagi...rumus yg >> saya tulis tdi hilang lagi...jadi bingung *garukgarukkepala* >> >> Trus mnurut Mr Kid klo saya pake openoffice pake rumus apa yg bisa >> compatible?...tpi smoga rumus tetep tu... >> Mumet rumusnya...hehehe... >> >> Makasih banyak Pak resporn cepatnya...mohon bantuannya Pak yha... >> >> best regards, >> Anton... >> *From:* mailto:belajar-excel@yahoogroups.com<belajar-excel@yahoogroups.com> >> *Sent:* Monday, May 19, 2014 10:10 AM >> *To:* BeExcel <belajar-excel@yahoogroups.com> >> *Subject:* Re: [belajar-excel] Apply rumus dari excel ke open office kok >> error >> >> >> Hai Anton, >> >> Terus terang, kalau lihat di file Excelnya, formulanya berbunyi =NA() dan >> pasti menghasilkan error value #N/A. Andai fungsi NA() dalam cell tersebut >> dihilangkan, berarti tidak muncul #N/A lagi kan ya... >> >> Atau sebenarnya ada formula Excel yang menggunakan fungsi yang tidak ada >> di openoffice ? Nah formula Excel yang menyebabkan di openoffice jadi >> keluar error value itu bunyinya seperti apa ya ? >> >> Wassalam, >> Kid. >> >> >> >> >> >> On Mon, May 19, 2014 at 9:06 AM, 'Fendik Anton Wijaya' >> fendik.an...@tafinance.com [belajar-excel] <belajar-excel@yahoogroups.com >> > wrote: >> >>> >>> Dear para suhu Be Exelers, >>> Mohon bantuannya para suhu , saya ada file (terlampir) klo saya buka di >>> openoffice malah muncul #n/a tpi klo di excel fine2 aj. >>> Klo saya ketik rumus sesuai yg diexcel itu malah muncul #name? di >>> openoffice trus klo saya save rumus di openoffice jg g kesimpan. >>> Parahnya lagi fasilitas kantor cuma ada open office aj. >>> >>> Mohon bantuannya para suhu kenapa y kira2?...saya coba setting regional >>> & language msh gtu jg... >>> Mohon pencerahannya solusi dari para suhu be excelers... >>> >>> terima kasih banyak yha... >>> >>> Best Rgds, >>> Anton... >>> >>> ------------------------------ >>> The information transmitted is intended only for the person or the >>> entity to which it is addressed and may contain confidential and/or >>> privileged material. If you have received it by mistake please notify the >>> sender by return e-mail and delete this message including any of its >>> attachments from your system. Any use, review, reliance or dissemination of >>> this message/information in whole or in part is strictly prohibited. Please >>> note that e-mails are susceptible to change. The views expressed/presented >>> in this email are solely those of the author and do not necessarily >>> represent those of PT Toyota Astra Financial Services and should not be >>> construed as the views, offers or acceptances of PT Toyota Astra Financial >>> Services. The recipient should check this email/message and any attachments >>> for the presence of viruses. PT Toyota Astra Financial Services accepts no >>> liability for any damage caused by any viruses transmitted by this >>> email/message. >>> >>> ý *Towards a Sustainable Future - **Please consider the environment >>> before printing this email.* >>> >> >> >> ------------------------------ >> The information transmitted is intended only for the person or the entity >> to which it is addressed and may contain confidential and/or privileged >> material. If you have received it by mistake please notify the sender by >> return e-mail and delete this message including any of its attachments from >> your system. Any use, review, reliance or dissemination of this >> message/information in whole or in part is strictly prohibited. Please note >> that e-mails are susceptible to change. The views expressed/presented in >> this email are solely those of the author and do not necessarily represent >> those of PT Toyota Astra Financial Services and should not be construed as >> the views, offers or acceptances of PT Toyota Astra Financial Services. The >> recipient should check this email/message and any attachments for the >> presence of viruses. PT Toyota Astra Financial Services accepts no >> liability for any damage caused by any viruses transmitted by this >> email/message. >> >> ý *Towards a Sustainable Future - **Please consider the environment >> before printing this email.* >> > > > > ------------------------------ > The information transmitted is intended only for the person or the entity > to which it is addressed and may contain confidential and/or privileged > material. If you have received it by mistake please notify the sender by > return e-mail and delete this message including any of its attachments from > your system. Any use, review, reliance or dissemination of this > message/information in whole or in part is strictly prohibited. Please note > that e-mails are susceptible to change. The views expressed/presented in > this email are solely those of the author and do not necessarily represent > those of PT Toyota Astra Financial Services and should not be construed as > the views, offers or acceptances of PT Toyota Astra Financial Services. The > recipient should check this email/message and any attachments for the > presence of viruses. PT Toyota Astra Financial Services accepts no > liability for any damage caused by any viruses transmitted by this > email/message. > > � *Towards a Sustainable Future - **Please consider the environment > before printing this email.* > > > ------------------------------ > The information transmitted is intended only for the person or the entity > to which it is addressed and may contain confidential and/or privileged > material. If you have received it by mistake please notify the sender by > return e-mail and delete this message including any of its attachments from > your system. Any use, review, reliance or dissemination of this > message/information in whole or in part is strictly prohibited. Please note > that e-mails are susceptible to change. The views expressed/presented in > this email are solely those of the author and do not necessarily represent > those of PT Toyota Astra Financial Services and should not be construed as > the views, offers or acceptances of PT Toyota Astra Financial Services. The > recipient should check this email/message and any attachments for the > presence of viruses. PT Toyota Astra Financial Services accepts no > liability for any damage caused by any viruses transmitted by this > email/message. > > ý *Towards a Sustainable Future - **Please consider the environment > before printing this email.* > > >