Generisk formel
=SUMPRODUCT((range=criteria)*(SUBTOTAL(103,OFFSET(range,rows,0,1))))
Resumé
For kun at tælle synlige rækker med kriterier kan du bruge en ret kompleks formel baseret på SUMPRODUCT, SUBTOTAL og OFFSET. I det viste eksempel er formlen i C12:
=SUMPRODUCT((C5:C8=C10)*(SUBTOTAL(103,OFFSET(C5,ROW(C5:C8)-MIN(ROW(C5:C8)),0))))
Forord
SUBTOTAL-funktionen kan let generere summer og optællinger for skjulte og ikke-skjulte rækker. Det er dog ikke i stand til at håndtere kriterier som COUNTIF eller SUMIF uden hjælp. En løsning er at bruge SUMPRODUCT til at anvende både SUBTOTAL-funktionen (via OFFSET) og kriterierne. Detaljerne i denne fremgangsmåde er beskrevet nedenfor.
Forklaring
I kernen fungerer denne formel ved at oprette to arrays inde i SUMPRODUCT. Den første matrix anvender kriterier, og den anden matrix håndterer synlighed:
=SUMPRODUCT(criteria*visibility)
Kriterierne anvendes med en del af formlen:
=(C5:C8=C10)
Hvilket genererer en matrix som denne:
(FALSE;TRUE;FALSE;TRUE)
Hvor SAND betyder "opfylder kriterier". Bemærk, fordi vi bruger multiplikation (*) på dette array, vil de SANDE FALSE-værdier automatisk blive konverteret til 1'er og 0'er ved matematikoperationen, så vi ender med:
(0;1;0;1)
Synlighedsfilteret påføres ved hjælp af SUBTOTAL med funktionsnummer 103.
SUBTOTAL er i stand til at ekskludere skjulte rækker, når der køres beregninger, så vi kan bruge det i dette tilfælde til at generere et "filter" til at ekskludere skjulte rækker inde i SUMPRODUCT. Problemet er dog, at SUBTOTAL returnerer et enkelt nummer, mens vi har brug for en række resultater for at bruge det med succes inden i SUMPRODUCT. Tricket er at bruge OFFSET til at fodre SUBTOTAL en reference pr. Række, så OFFSET returnerer et resultat pr. Række.
Selvfølgelig kræver det et andet trick, som er at give OFFSET et array, der indeholder et tal pr. Række, begyndende med nul. Vi gør det med et udtryk bygget på ROW-funktionen:
=ROW(C5:C8)-MIN(ROW(C5:C8)
som vil generere en matrix som denne:
(0;1;2;3)
Sammenfattende genereres det andet array (som håndterer synlighed ved hjælp af SUBTOTAL) sådan:
=SUBTOTAL(103,OFFSET(C5,ROW(C5:C8)-MIN(ROW(C5:C8)),0)) =SUBTOTAL(103,OFFSET(C5,(0;1;2;3),0)) =SUBTOTAL(103,("East";"West";"Midwest";"West")) =(1;0;1;1)
Og endelig har vi:
=SUMPRODUCT((0,1,0,1)*(1;0;1;1))
Hvilket returnerer 1.
Flere kriterier
Du kan udvide formlen til at håndtere flere kriterier som dette:
=SUMPRODUCT((rng1=criteria1)*(rng2=criteria2)*(SUBTOTAL(103,OFFSET(rng,rows,0,1))))
Sammenfatningsresultater
For at returnere en sum af værdier i stedet for et antal kan du tilpasse formlen til at inkludere et sumområde:
=SUMPRODUCT(criteria*visibility*sumrange)
Kriterierne og synlighedsarrayerne fungerer på samme måde som forklaret ovenfor, undtagen celler, der ikke er synlige. Hvis du har brug for delvis matching, kan du konstruere et udtryk ved hjælp af ISNUMBER + SEARCH, som forklaret her.
