Excel

SUMIF dengan pelbagai kriteria dan logik ATAU

Sumifs With Multiple Criteria

Formula Excel: SUMIFS dengan pelbagai kriteria dan logik ATAUFormula generik
= SUM ( SUMIFS (sum_range,criteria_range,{'red','blue'}))
Ringkasan

Untuk jumlah berdasarkan beberapa kriteria menggunakan logik ATAU, anda boleh menggunakan fungsi SUMIFS dengan pemalar larik. Dalam contoh yang ditunjukkan, formula dalam H6 adalah:



 
= SUM ( SUMIFS (E4:E11,D4:D11,{'complete','pending'}))
Penjelasan

Secara lalai, fungsi SUMIFS hanya membenarkan logik AND - apabila anda menyediakan beberapa syarat, semua syarat mesti sepadan untuk dimasukkan dalam hasilnya.

Salah satu penyelesaiannya adalah dengan menyediakan pelbagai kriteria dalam pemalar larik seperti ini:





excel menukar masa perpuluhan menjadi jam dan minit
 
{'complete','pending'}

Ini akan menyebabkan SUMIFS mengembalikan dua hasil: kiraan untuk 'lengkap' dan kiraan untuk 'belum selesai', dalam hasil array seperti ini:

 
{100,35}

Untuk mendapatkan jumlah akhir, kami membungkus SUMIFS di dalam SUM. Fungsi SUM menjumlahkan semua item dalam array dan mengembalikan hasilnya.



Dengan kad liar

Anda boleh menggunakan wildcard dalam kriteria jika diperlukan. Sebagai contoh, untuk menjumlahkan item yang mengandungi 'merah' atau 'biru' di mana sahaja dalam julat_kriteria, anda boleh menggunakan:

bagaimana melakukan vlookup dengan dua hamparan
 
= SUM ( SUMIFS (sum_range,criteria_range,{'*red*','*blue*'}))

Menambah kriteria ATAU yang lain

Anda boleh menambahkan satu kriteria tambahan untuk formula ini, tetapi anda perlu menggunakan a susunan lajur tunggal untuk satu kriteria dan satu baris susunan untuk yang lain. Jadi, sebagai contoh, untuk menjumlahkan pesanan yang 'Selesai' atau 'Tertunda', untuk 'Andy Garcia' atau 'Bob Jones', anda boleh menggunakan:

 
= SUM ( SUMIFS (E4:E11,D4:D11,{'complete','pending'},C4:C11,{'Bob Jones''Andy Garcia'}))

Perhatikan titik koma di kedua pemalar larik, yang mewakili tatasusunan menegak. Ini berfungsi kerana elemen 'pasangan' Excel dalam dua pemalar larik, dan mengembalikan hasil keputusan dua dimensi. Dengan lebih banyak kriteria, anda ingin beralih ke a formula berdasarkan SUMPRODUCT .

jika dan berfungsi dalam excel 2010

Rujukan sel untuk kriteria

Anda tidak boleh menggunakan rujukan sel di dalam pemalar array. Untuk menggunakan rujukan sel untuk kriteria, anda boleh menggunakan formula larik seperti ini:

 
={ SUM ( SUMIFS (range1,range2,range3))}

Di mana range1 adalah rentang jumlah, range2 adalah rentang kriteria, dan range3 mengandungi kriteria pada lembaran kerja. Dengan dua kriteria ATAU, anda perlu menggunakan tatasusunan mendatar dan menegak.

Catatan: ini adalah formula larik dan mesti dimasukkan dengan kawalan + shift + enter.

Pengarang Dave Bruns


^