Formula umum
=SUMPRODUCT(--(MOD(COLUMN(rng)-COLUMN(rng.first)+1,n)=0),rng)
Ringkasan
Untuk menjumlahkan setiap kolom ke-n, Anda dapat menggunakan rumus berdasarkan fungsi SUMPRODUCT, MOD, dan COLUMN.
Dalam contoh yang ditunjukkan, rumus di L5 adalah:
=SUMPRODUCT(--(MOD(COLUMN(B5:J5)-COLUMN(B5)+1,K5)=0),B5:J5)
Penjelasan
Pada intinya, gunakan SUMPRODUCT untuk menjumlahkan nilai dalam baris yang telah "difilter" menggunakan logika berdasarkan MOD. Kuncinya adalah ini:
MOD(COLUMN(B5:J5)-COLUMN(B5)+1,K5)=0
Cuplikan rumus ini menggunakan fungsi COLUMN untuk mendapatkan sekumpulan nomor kolom "relatif" untuk rentang tersebut (dijelaskan secara mendetail di sini) yang terlihat seperti ini:
(1,2,3,4,5,6,7,8,9)
Ini masuk ke MOD seperti ini:
MOD((1,2,3,4,5,6,7,8,9),K5)=0
dimana K5 adalah nilai N pada setiap baris. Fungsi MOD mengembalikan sisa untuk setiap nomor kolom dibagi dengan N. Jadi, misalnya, ketika N = 3, MOD akan mengembalikan sesuatu seperti ini:
(1,2,0,1,2,0,1,2,0)
Perhatikan bahwa angka nol muncul untuk kolom 3, 6, 9, dll. Rumus menggunakan = 0 untuk memaksa TRUE ketika sisanya adalah nol dan FALSE jika tidak, maka kita menggunakan negatif ganda (-) untuk memaksa TRUE dan SALAH menjadi satu dan nol. Itu meninggalkan array seperti ini:
(0,0,1,0,0,1,0,0,1)
Di mana 1 sekarang menunjukkan "nilai n". Ini masuk ke SUMPRODUCT sebagai array1, bersama dengan B5: J5 sebagai array2. SUMPRODUCT kemudian melakukan tugasnya, pertama kali mengalikan, lalu menjumlahkan produk dari array.
Satu-satunya nilai yang "bertahan" dari perkalian adalah yang mana array1 berisi 1. Dengan cara ini, Anda dapat memikirkan logika array1 yang "memfilter" nilai-nilai dalam array2.
Jumlahkan setiap kolom lainnya
Jika Anda ingin menjumlahkan setiap kolom lainnya, cukup sesuaikan rumus ini sesuai kebutuhan, ingatlah bahwa rumus tersebut secara otomatis menetapkan 1 ke kolom pertama dalam rentang. Untuk menjumlahkan EVEN kolom, gunakan:
=SUMPRODUCT(--(MOD(COLUMN(A1:Z1)-COLUMN(A1)+1,2)=0),A1:Z1)
Untuk menjumlahkan kolom ODD, gunakan:
=SUMPRODUCT(--(MOD(COLUMN(A1:Z1)-COLUMN(A1)+1,2)=1),A1:Z1)
