Generieke formule
=SUMPRODUCT(--(MOD(COLUMN(rng)-COLUMN(rng.first)+1,n)=0),rng)
Samenvatting
Om elke n-de kolom op te tellen, kunt u een formule gebruiken die is gebaseerd op de functies SOMPRODUCT, MOD en COLUMN.
In het getoonde voorbeeld is de formule in L5:
=SUMPRODUCT(--(MOD(COLUMN(B5:J5)-COLUMN(B5)+1,K5)=0),B5:J5)
Uitleg
Gebruikt in de kern SOMPRODUCT om waarden in een rij op te tellen die zijn "gefilterd" met behulp van logica op basis van MOD. De sleutel is dit:
MOD(COLUMN(B5:J5)-COLUMN(B5)+1,K5)=0
Dit fragment van de formule gebruikt de COLUMN-functie om een set "relatieve" kolomnummers te krijgen voor het bereik (hier in detail uitgelegd) dat er als volgt uitziet:
(1,2,3,4,5,6,7,8,9)
Dit gaat als volgt in MOD:
MOD((1,2,3,4,5,6,7,8,9),K5)=0
waarbij K5 de waarde is voor N in elke rij. De MOD-functie retourneert de rest voor elk kolomnummer gedeeld door N. Dus als N = 3, zal MOD bijvoorbeeld iets als dit retourneren:
(1,2,0,1,2,0,1,2,0)
Merk op dat de nullen verschijnen voor kolom 3, 6, 9, enz. De formule gebruikt = 0 om een WAAR te forceren als de rest nul is en een ONWAAR als dat niet het geval is, dan gebruiken we een dubbel negatief (-) om WAAR en ONWAAR voor enen en nullen. Dat laat een array als volgt achter:
(0,0,1,0,0,1,0,0,1)
Waar 1s nu "n-de waarden" aangeven. Dit gaat in SOMPRODUCT als array1, samen met B5: J5 als array2. SOMPRODUCT doet dan zijn ding, eerst vermenigvuldigt en vervolgens producten van de arrays optellen.
De enige waarden die vermenigvuldiging "overleven" zijn die waar array1 1 bevat. Op deze manier kun je denken aan de logica van array1 die de waarden in array2 "filtert".
Tel elke andere kolom bij elkaar op
Als u elke andere kolom wilt optellen, past u deze formule zo nodig aan, waarbij u er rekening mee houdt dat de formule automatisch 1 toewijst aan de eerste kolom in het bereik. Gebruik om EVEN kolommen bij elkaar op te tellen:
=SUMPRODUCT(--(MOD(COLUMN(A1:Z1)-COLUMN(A1)+1,2)=0),A1:Z1)
Gebruik om ODD-kolommen bij elkaar op te tellen:
=SUMPRODUCT(--(MOD(COLUMN(A1:Z1)-COLUMN(A1)+1,2)=1),A1:Z1)
