Generisk formel
=SUMPRODUCT(--(range1=criteria),range2)
Resumé
For at opsummere eller subtotale kolonner baseret på kriterier i tilstødende kolonner kan du bruge en formel baseret på SUMPRODUCT-funktionen. I det viste eksempel er formlen i J5:
=SUMPRODUCT(--($B5:$H5=J$4),$C5:$I5)
Forklaring
I kernen bruger denne formel SUMPRODUCT til at multiplicere og derefter summe produkter af to arrays: array1 og array2 . Den første matrix, array1 er indstillet til at fungere som et "filter" for kun at tillade værdier, der opfylder kriterierne.
Array1 bruger et område, der begynder i den første kolonne, der indeholder værdier, der skal opfylde kriterier. Disse "kriterieværdier" sidder i en kolonne til venstre for og umiddelbart ved siden af "dataværdierne".
Kriterierne anvendes som en simpel test, der opretter en matrix med SANDE og FALSKE værdier:
--($B5:$H5=J$4)
Denne bit af formlen "tester" hver værdi i den første matrix ved hjælp af de leverede kriterier og bruger derefter en dobbelt-negativ (-) til at tvinge de resulterende Sande og FALSE-værdier til 1 og 0. Resultatet ser sådan ud:
(1,0,0,0,1,0,1)
Bemærk, at 1'er svarer til kolonne 1,5 og 7, der opfylder kriterierne for "A".
For array2 inde i SUMPRODUCT bruger vi et interval, der "forskydes" med en kolonne til højre. Dette interval starter med den første kolonne, der indeholder værdier til sum, og slutter med den sidste kolonne, der indeholder værdier, der skal summeres.
Så i eksempelformlen i J5, efter at arrays er blevet befolket, har vi:
=SUMPRODUCT((1,0,0,0,1,0,1),(1,"B",1,"A",1,"A",1))
Da SUMPRODUCT er programmeret specifikt til at ignorere de fejl, der skyldes multiplikation af tekstværdier, ser den endelige matrix sådan ud:
(1,0,0,0,1,0,1)
De eneste værdier, der "overlever" multiplikation, er de, der svarer til 1'ere i array1 . Du kan tænke på logikken i array1 "filtrering" af værdierne i array2 .
