Generisk formel
=SUMPRODUCT((range=criteria)*(SUBTOTAL(103,OFFSET(range,rows,0,1))))
Sammendrag
For å telle synlige rader bare med kriterier, kan du bruke en ganske kompleks formel basert på SUMPRODUCT, SUBTOTAL og OFFSET. I eksemplet vist er formelen i C12:
=SUMPRODUCT((C5:C8=C10)*(SUBTOTAL(103,OFFSET(C5,ROW(C5:C8)-MIN(ROW(C5:C8)),0))))
Forord
SUBTOTAL-funksjonen kan enkelt generere summer og teller for skjulte og ikke-skjulte rader. Det er imidlertid ikke i stand til å håndtere kriterier som COUNTIF eller SUMIF uten hjelp. En løsning er å bruke SUMPRODUCT til å bruke både SUBTOTAL-funksjonen (via OFFSET) og kriteriene. Detaljene i denne tilnærmingen er beskrevet nedenfor.
Forklaring
I kjernen fungerer denne formelen ved å sette opp to matriser inne i SUMPRODUCT. Den første matrisen bruker kriterier, og den andre matrisen håndterer synlighet:
=SUMPRODUCT(criteria*visibility)
Kriteriene brukes med en del av formelen:
=(C5:C8=C10)
Som genererer en matrise som dette:
(FALSE;TRUE;FALSE;TRUE)
Hvor SANT betyr "oppfyller kriterier". Merk fordi vi bruker multiplikasjon (*) på denne matrisen, vil de SANTE FALSKE verdiene automatisk konverteres til 1 og 0 ved matematikkoperasjonen, så vi ender med:
(0;1;0;1)
Synlighetsfilteret brukes med SUBTOTAL, med funksjonsnummer 103.
SUBTOTAL er i stand til å ekskludere skjulte rader når du kjører beregninger, så vi kan bruke det i dette tilfellet til å generere et "filter" for å ekskludere skjulte rader inne i SUMPRODUCT. Problemet er imidlertid at SUBTOTAL returnerer et enkelt tall, mens vi trenger en rekke resultater for å bruke det med suksess i SUMPRODUCT. Trikset er å bruke OFFSET for å mate SUBTOTAL en referanse per rad, slik at OFFSET vil returnere ett resultat per rad.
Selvfølgelig krever det et annet triks, som er å gi OFFSET en matrise som inneholder ett tall per rad, og starter med null. Vi gjør det med et uttrykk bygget på ROW-funksjonen:
=ROW(C5:C8)-MIN(ROW(C5:C8)
som vil generere en matrise som dette:
(0;1;2;3)
Oppsummert genereres den andre matrisen (som håndterer synlighet ved hjelp av SUBTOTAL) slik:
=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 til slutt har vi:
=SUMPRODUCT((0,1,0,1)*(1;0;1;1))
Som returnerer 1.
Flere kriterier
Du kan utvide formelen for å håndtere flere kriterier som dette:
=SUMPRODUCT((rng1=criteria1)*(rng2=criteria2)*(SUBTOTAL(103,OFFSET(rng,rows,0,1))))
Summing resultater
For å returnere en sum av verdier i stedet for en telling, kan du tilpasse formelen slik at den inkluderer et sumområde:
=SUMPRODUCT(criteria*visibility*sumrange)
Kriteriene og synlighetsarrayene fungerer på samme måte som forklart ovenfor, unntatt celler som ikke er synlige. Hvis du trenger delvis samsvar, kan du konstruere et uttrykk ved hjelp av ISNUMBER + SEARCH, som forklart her.
