Formula generica
=SUMPRODUCT((range=criteria)*(SUBTOTAL(103,OFFSET(range,rows,0,1))))
Sommario
Per contare le righe visibili solo con criteri, puoi utilizzare una formula piuttosto complessa basata su SUMPRODUCT, SUBTOTAL e OFFSET. Nell'esempio mostrato, la formula in C12 è:
=SUMPRODUCT((C5:C8=C10)*(SUBTOTAL(103,OFFSET(C5,ROW(C5:C8)-MIN(ROW(C5:C8)),0))))
Prefazione
La funzione SUBTOTALE può generare facilmente somme e conteggi per righe nascoste e non nascoste. Tuttavia, non è in grado di gestire criteri come CONTA.SE o SOMMA.SE senza aiuto. Una soluzione è utilizzare SUMPRODUCT per applicare sia la funzione SUBTOTALE (tramite OFFSET) che i criteri. I dettagli di questo approccio sono descritti di seguito.
Spiegazione
Fondamentalmente, questa formula funziona impostando due array all'interno di SUMPRODUCT. Il primo array applica i criteri e il secondo array gestisce la visibilità:
=SUMPRODUCT(criteria*visibility)
Il criterio si applica con parte della formula:
=(C5:C8=C10)
Che genera un array come questo:
(FALSE;TRUE;FALSE;TRUE)
Dove TRUE significa "soddisfa i criteri". Nota perché stiamo usando la moltiplicazione (*) su questo array, i valori TRUE FALSE verranno automaticamente convertiti in 1 e 0 dall'operazione matematica, quindi finiamo con:
(0;1;0;1)
Il filtro di visibilità viene applicato utilizzando SUBTOTALE, con il numero di funzione 103.
SUBTOTAL è in grado di escludere righe nascoste durante l'esecuzione di calcoli, quindi possiamo usarlo in questo caso per generare un "filtro" per escludere righe nascoste all'interno di SUMPRODUCT. Il problema però è che SUBTOTAL restituisce un singolo numero, mentre abbiamo bisogno di un array di risultati per usarlo con successo all'interno di SUMPRODUCT. Il trucco è usare OFFSET per alimentare SUBTOTAL un riferimento per riga, in modo che OFFSET restituisca un risultato per riga.
Ovviamente, ciò richiede un altro trucco, che consiste nel fornire a OFFSET un array che contiene un numero per riga, a partire da zero. Lo facciamo con un'espressione costruita sulla funzione ROW:
=ROW(C5:C8)-MIN(ROW(C5:C8)
che genererà un array come questo:
(0;1;2;3)
In sintesi, il secondo array (che gestisce la visibilità utilizzando SUBTOTAL), viene generato in questo modo:
=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)
E, infine, abbiamo:
=SUMPRODUCT((0,1,0,1)*(1;0;1;1))
Che restituisce 1.
Criteri multipli
Puoi estendere la formula per gestire più criteri come questo:
=SUMPRODUCT((rng1=criteria1)*(rng2=criteria2)*(SUBTOTAL(103,OFFSET(rng,rows,0,1))))
Sommare i risultati
Per restituire una somma di valori invece di un conteggio, puoi adattare la formula per includere un intervallo di somma:
=SUMPRODUCT(criteria*visibility*sumrange)
I criteri e gli array di visibilità funzionano come spiegato sopra, escludendo le celle che non sono visibili. Se hai bisogno di una corrispondenza parziale, puoi costruire un'espressione utilizzando ISNUMBER + SEARCH, come spiegato qui.
