Generisk formel
=SUMPRODUCT((range=criteria)*(SUBTOTAL(103,OFFSET(range,rows,0,1))))
Sammanfattning
För att bara räkna synliga rader med kriterier kan du använda en ganska komplex formel baserad på SUMPRODUCT, SUBTOTAL och OFFSET. I exemplet som visas är formeln i C12:
=SUMPRODUCT((C5:C8=C10)*(SUBTOTAL(103,OFFSET(C5,ROW(C5:C8)-MIN(ROW(C5:C8)),0))))
Förord
SUBTOTAL-funktionen kan enkelt generera summor och räkningar för dolda och icke-dolda rader. Det kan dock inte hantera kriterier som COUNTIF eller SUMIF utan hjälp. En lösning är att använda SUMPRODUCT för att tillämpa både SUBTOTAL-funktionen (via OFFSET) och kriterierna. Detaljerna i detta tillvägagångssätt beskrivs nedan.
Förklaring
Kärnan fungerar den här formeln genom att ställa in två matriser inuti SUMPRODUCT. Den första matrisen tillämpar kriterier och den andra matrisen hanterar synlighet:
=SUMPRODUCT(criteria*visibility)
Kriterierna tillämpas med en del av formeln:
=(C5:C8=C10)
Vilket genererar en array så här:
(FALSE;TRUE;FALSE;TRUE)
Där SANT betyder "uppfyller kriterier". Observera att eftersom vi använder multiplikation (*) på den här matrisen, kommer de SANNA FALSKA värdena automatiskt att konverteras till 1 och 0 genom matteoperationen, så vi slutar med:
(0;1;0;1)
Synlighetsfiltret appliceras med SUBTOTAL, med funktionsnummer 103.
SUBTOTAL kan utesluta dolda rader när beräkningar körs, så vi kan använda det i det här fallet för att generera ett "filter" för att utesluta dolda rader inuti SUMPRODUCT. Problemet är dock att SUBTOTAL returnerar ett enda nummer, medan vi behöver en rad resultat för att kunna använda det framgångsrikt i SUMPRODUCT. Tricket är att använda OFFSET för att mata SUBTOTAL en referens per rad, så att OFFSET returnerar ett resultat per rad.
Naturligtvis kräver det ett annat knep, vilket är att ge OFFSET en matris som innehåller ett nummer per rad, som börjar med noll. Vi gör det med ett uttryck som bygger på ROW-funktionen:
=ROW(C5:C8)-MIN(ROW(C5:C8)
som genererar en matris som denna:
(0;1;2;3)
Sammanfattningsvis genereras den andra matrisen (som hanterar synlighet med SUBTOTAL) så här:
=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)
Och slutligen har vi:
=SUMPRODUCT((0,1,0,1)*(1;0;1;1))
Som returnerar 1.
Flera kriterier
Du kan utöka formeln för att hantera flera kriterier så här:
=SUMPRODUCT((rng1=criteria1)*(rng2=criteria2)*(SUBTOTAL(103,OFFSET(rng,rows,0,1))))
Sammanfattningsresultat
För att returnera en summa av värden istället för ett antal kan du anpassa formeln så att den inkluderar ett summaområde:
=SUMPRODUCT(criteria*visibility*sumrange)
Kriterierna och synlighetsarrayerna fungerar på samma sätt som förklarats ovan, exklusive celler som inte är synliga. Om du behöver partiell matchning kan du skapa ett uttryck med ISNUMBER + SEARCH, som förklaras här.
