Immaginiamo di avere un intervallo di celle in Excel, B5:B20, e di voler sommare le celle di questo intervallo in base alla loro formattazione; ad esempio abbiamo bisogno di sommare tutte le celle che hanno il carattere o lo sfondo rosso e il testo in grassetto.
Prima di tutto si deve lavorare tramite codice VBA quindi apriamo la relativa finestra.
In Excel XP o 2003:
Menù Strumenti – Macro – Visual Basic Editor
In Excel 2007:
Bottone di Office – Pulsante Opzioni di Excel – Impostazioni generali – Opzione “Mostra scheda Sviluppo sulla barra multifunzione”
A questo punto ritornando nella finestra di Excel verrà visualizzata anche la Scheda Sviluppo nella quale troviamo il pulsante Visual Basic
Nella finestra Visual Basic inserire un nuovo modulo (menù Inserisci – Modulo)
Copiare il codice riportato sotto:
Function FormatSum(rCriteriaCell As Range, rRange As Range)
Dim rcell As Range
Dim bMatch As Boolean
Dim vResult
For Each rcell In rRange
With rcell
bMatch = (rCriteriaCell.Interior.ColorIndex = _
.Interior.ColorIndex And _
rCriteriaCell.Font.ColorIndex = _
.Font.ColorIndex And _
rCriteriaCell.Font.Bold = _
.Font.Bold And _
rCriteriaCell.Font.Italic = _
.Font.Italic And _
rCriteriaCell.Font.Underline = .Font.Underline)
End With
If bMatch = True Then
vResult = WorksheetFunction.Sum(rcell) + vResult
End If
Next rcell
FormatSum = vResult
End Function
Chiudere la finestra e ritornare in Excel. La funzione che deve essere inserita per sommare le celle secondo il loro formato è la seguente:
=FormatSum(Formato;Intervallo)
In cui:
Formato: La cella contenente il formato di riferimento
Intervallo: Le effettive celle da sommare
Esempio: =FormatSum($B$5;B5:B20)
Somma, nell’intervallo B5:B20, tutte le celle che hanno il formato presente nella cella B5 (esempio testo o sfrondo rosso in grassetto).
Nel nostro esempio la cella del formato può essere qualsiasi cella, contenente qualsiasi formato.
P.S.
Se nella cella di riferimento viene modificato il formato, l’aggiornamento del risultato della somma non è automatico; il cambiamento di un dato nelle celle dell’intervallo da sommare farà si che il risultato venga ricalcolato.
Invece il metodo più veloce per far si che la somma si aggiorni automaticamente quando cambiate il formato delle celle nell’intervallo, è quello di applicare il formato nelle celle che dovranno essere aggiunte alla somma copiandolo da una cella già formattata utilizzando il pulsante Copia Formato presente sulla barra degli strumenti di Formattazione.
Informatics, ski racer, loves travels, Finland, Norway, mountains, squash, photography, Drones and RC Helicopter
RSS - Articoli
Grazie mille!!! E' perfetto!
se la formattazione è condizionale non funziona. esiste il modo di contare le celle con formattazione condizionale?
Mitico!!
Utilissimo, molto chiaro e con risultato perfetto!
Grazie mille!
Una domanda… se volessi Contare le celle con una certa formattazione??
Come devo modificare il codice? è possibile farlo?
Ho guardato il tuo post sul conteggio per colore ma mi servirebbe in base alla formattazione…
Grazie mille
@Paola: se vuoi contare le celle con una formattazione puoi semplicemente utilizzare lo stesso codice di questo post sostituendo nel codice stesso Sum con Count
Se non è chiaro mandami una mail che ti invio il codice
ciao! voglio contare celle colorate di giallo che abbiano un valore <90 ma non riesco a farlo, mi puoi aiutare?
mille grazie
@andrea: ipotizziamo che tu abbia un intervallo di celle che vanno dalla D3 alla D100 con colori differenti e vuoi contare solo le celle gialle con valore minore di 90. Puoi prima filtrare le celle per colore (Excel 2010 ti permette di filtrare per colore) e poi per contare solo le celle visibili (che quindi saranno sono le celle gialle) con valore <90 devi usare questa funzione:
=MATR.SOMMA.PRODOTTO(SUBTOTALE(3;SCARTO(D3:D100;RIF.RIGA(D3:D100)-MIN(RIF.RIGA(D3:D100));;1))*(D3:D100<90))
@andrea perotti: io ho 15 celle, da B3 a P3. Alcune hanno lo sfondo giallo e altre no. Io voglio contare quante di queste celle gialle possiedono un valore inferiore a 90 (quindi il risultato della funzione dovrebbe rientrare da 0 a 15). La tua funzione, adattata al mio intervallo B3:P3, mi da sempre un valore di 300, se tolgo una cella toglie 20 numeri e indica 280, se ne tolgo un’altra indica 260 e così via. Se cambio il valore 3 alla funzione, quello che indica il numero della funzione, non arrivo mai al risultato sperato. Sigh sigh riesci ancora ad aiutarmi?
Pensa che poi mi serve anche una funzione che, nello stesso intervallo, mi consideri solo le celle senza colore di sfondo oppure non di sfondo giallo e mi conti quante di queste hanno valore compreso tra 1 e 89!
Spero tu riesca ad aiutarmi, io non so più che pesci pigliare!
Mille grazie!!!!
@andrea: la formula che ho scritto fa quello che tu chiedi, su un intervallo filtrato. Se hai Excel 2010 basta filtrare per colore le celle e alla fine della formula applicare il criterio. Eventualmente mandami per mail il file che ti imposto la formula.
Ciao Andrea, intanto complimenti. Senti, io ho copiato il modulino (ho Excel 2010) e poi ho provato la formula, ma NON va (ho un “calendario” con 6 colonne, in cui sono evidenziati con sfondi diversi i giorni di diverse conferenze e li vorrei contare!)
Non ho mai programmato in VB, dici che dovrei controllare la sintassi? (tipo se ci sono spazi di troppo oppure altro?)
@Luigi: così è un po’ difficile dire dov’è il problema. In ogni caso se segui la procedura come descritta la formula funziona.
ciao andrea!
ho inserito il modulo in visual basic e poi la formula come mi hai indicato, FUNZIONA!!!!!!!
poi, addirittura, l’ho riutilizzata; cambiando nome e segno maggiore, per un’altra cosa.
che dire……1000 e 1000 e 1000 grazie per la tua cortesia e competenza!
ciao e…..alla prossima! 🙂
andrea
Ciao,
inanzi tutto grazie mille ,questa formula mi è stata utilissima per lavorare su un foglio di lavoro veramente ampio però avrei una domanda.
Se decido di aggiungere in un secondo momento un altra cella con la medesima formattazione delle altre sommate questa non mi viene contata , almeno non in tempo reale cotringendomi a selezionare la formula e a premere “invio” affinchè i valori vengano aggiornati.
cerco di spiegarmi meglio:
– sommo le celle
– una volta sommate ne aggiungo una nuova ma il totale non viene aggiornato in tempo reale
è possibile fixare la cosa?
Ok ma se uso con filtro con “subtotale” con colore rosso, Non riesco, qualcuno mi puoi aiutare.
Grazie
@Lupin: il ricalcolo automatico delle celle di un foglio avviene quando si modifica un dato in una cella non quando si cambia la formattazione, per questo motivo quando semplicemente si cambia il colore dello sfondo o del testo di una cella il risultato non cambia. Se si vuole che venga effettuato il ricalcolo allora si può aggiungere del codice che abiliti il ricalcolo del foglio ogni volta che ci si sposta in un’altra cella.
Nella finestra di Visual Basic si deve fare doppio clic sulla voce Questa_cartella_di_lavoro presente nel riquadro a sinistra. Quindi nella finestra che appare basterà aggiungere il codice seguente:
Option Explicit
Private Sub Workbook_Open()
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
ActiveSheet.Calculate
End Sub
Quindi si può chiudere la finestra di Visual Basic. Da questo momento il ricalcolo automatico verrà effettuato ogni volta che ci si sposta da una cella all’altra; questo però comporta rallentamenti nel caso in cui siano presenti nel foglio molte funzioni.
Mi puoi aiutare a costruire una funzione che colori le celle che seleziono in base al colore di una cella già formattata ovviamente senza l’uso della formattazione condizionale.
Grazie
@Silvano: appena ho tempo proverò a vedere come è possibile fare ciò che chiedi con una funzione, però perchè non fai la stessa cosa più semplicemnete usando la formattazione tramite stili?
Si ho già provato e ho usato anche la formattazione condizionale per costruire un gantt.. è abbastanza efficiente ma troppo rigido.. mentre vorrei poter cambiare il colore degli istogrammi in base a quello impostato nelle rispettive celle della tabella di corrispondenza senza riformattare ogni volta il gantt se cambio i colori.
@Silvano: a leggere Excel e Gantt insieme mi è venuto un brivido 😉 In ogni caso se trovo una funzione che faccia quello che cerchi te lo faccio sapere.
mi stupisce la tua osservazione, excel mi sembra più che sufficiente per fare gantt dato che in fondo si tratta di semplici grafici a barre.. grazie comunque dell’attenzione
Ecco, anche no! 😉 Silvano comunque ti consiglierei non dire a chi fa Project Management seriamente che il Gantt in fondo è un semplice grafico a barre, potrebbe come minimo picchiarti 🙂
Ho chiesto solo una funzione per copiare il colore da una cella ad un’altra…
Ciao
ho trovato la funzione utilissima, ma se volessi fare la somma solo su un tipo di formattazione (il colore di fondo delle celle) come dovrei modificare la funzione?
Grazie
@gianni60: non devi modificare la formula devi solo selezionare una cella che contiene la formattazione che ti interessa, ovvero che abbia il colore di sfondo per il quale vuoi che venga svolta la somma. Altrimenti se vuoi sommare celle in base al solo colore di sfondo o del testo puoi anche usare quest’altra formula: https://www.andreaperotti.ch/2007/05/sommare-o-contare-celle-colorate-in-excel/
Buongiorno Andrea,
grazie per queste ottime informazioni su excel: adesso dispongo di formatsum, countbycolor e sumbycolor.
C’è un modo per inserire anche la guida della funzione?
Giuseppe
@Giuseppe: puoi inserire una descrizione in questo modo: nella finestra di Visual Basic premi F2, quindi scegli VBAProject dal menù a discesa, nel riquadro a destra, in classi selezioni il modulo quindi clicca con il destro sul nome della funzione e scegli proprietà.
Ciao Andrea!
la mia domanda è semplice…
Es.: nella colonna A1:A10 ho delle celle il cui sfondo si colora a seguito di una formattazione condizionale.
La mia necessità è quella di contare o sommare (ad esempio nella cella A11) le celle che si sono colorate nella colonna A1:A10
Grazie mille!!!
Angelo
Salve, ho alcune celle colorate in arancione il range in cui si trovano le celle colorate va da (x10:gu4014)in x3 vorrei il totale
del valore che si trova a DX della cella colorata.
Grazie dell’interessamento
Salve,
io avrei bisogno di una funzione probabilmente più semplice di queste, che mi permetta di selezionare all’interno di un foglio solo le celle NON in grassetto, per poi cancellarle, o più semplicemente una funzione che mi cancella tutto ciò che non è in grassetto all’interno di un foglio excel.
C’è una formula che permette qualcosa del genere?
Grazie
complimenti al tuo blog seguo le tue avventure davvero interessanti.
mi potresti aiutare a risolvere un problema con excel?
Seguo le gare di trhiatlon di mio figlio e sono stato incaricato di fare una classifica automtica degli arrivi.
tutto va bene per la classsifcia assoluta ma mi occorre fare anche quella per categoria accanto a quella assoluta.
quella assoluta l’ho realizzata con la funzione “Rango” ma come fare con quella per categoria .. non so come fare ..
ho una tebella di questo tipo:
numero pettorale categoria tempo classifica assoluta classifica per categoria
resto in attesa grazie
Ciao Silvano
@Silvano: ipotizzando di avere questa situazione
Nelle colonne:
Colonna A: Pettorale
Colonna B: Nome
Colonna C: Categoria
Colonna D: Tempo
Colonna E: Classifica Assoluta (Da calcolare)
Colonna F: Classifica di Categoria (Da calcolare)
Nelle righe 6 valori di esempio
Le funzioni da utilizzare saranno:
in E2 per avere la classifica assoluta: =RANGO(D2;$D$2:$D$6;1)
in F2 per avere la classifica di categoria:
=1+MATR.SOMMA.PRODOTTO(($C$2:$C$6=C2)*($D$2:$D$6 < D2))
ciao sto cercando di utilizzare la tua formula per contare le celle colorate da formattazione
Function FormatCount(rCriteriaCell As Range, rRange As Range)
Dim rcell As Range
Dim bMatch As Boolean
Dim vResult
For Each rcell In rRange
With rcell
bMatch = (rCriteriaCell.Interior.ColorIndex = _
.Interior.ColorIndex And _
rCriteriaCell.Font.ColorIndex = _
.Font.ColorIndex And _
rCriteriaCell.Font.Bold = _
.Font.Bold And _
rCriteriaCell.Font.Italic = _
.Font.Italic And _
rCriteriaCell.Font.Underline = .Font.Underline)
End With
If bMatch = True Then
vResult = WorksheetFunction.Count(rcell) + vResult
End If
Next rcell
FormatCount = vResult
End Function
=FormatCount($AL$1;C6:AG6)
ma non mi funziona mi da sempre 0, ho provato anche con Sun e mi da errore, come posso risolvere?
grazie
@fernando: per contare in base alla formattazione puoi usare questa funzione: https://www.andreaperotti.ch/2015/02/contare-le-celle-in-un-intervallo-excel-corrispondenti-ad-una-specifica-formattazione/
Ciao,
la macro funziona molto bene. Quando chiudo excel e poi lo riapro, la funzione formatsum non funziona più. Dove devo copiare la macro per renderla effettiva ogni volta che apro excel?
Grazie mille
Andrea
@Andrea: se vuoi rendere disponibile la funzione anche in altri files di excel puoi creare un componente aggiuntivo seguendo queste istruzioni: https://www.andreaperotti.ch/2007/05/creare-un-componente-aggiuntivo-add-ins-per-excel/
Buongiorno Andrea,
ci provo…anche se sono passati un po’ di anni dalla tua pubblicazione.
Avrei necessità di sommare celle in grassetto all’interno di una colonna dove ho varie tipologie di formattazione, onde evitare di afre la somma a mano, potresti suggerirmi dei comandi per fare il calcolo.
Grazie
laura
Ciao, intanto vorrei ringraziare per i suggerimenti e le formule… Vorrei sapere come fare per far sì che una cella restituisca il Valore Massimo (ad esempio la data più recente) tra le sole celle che hanno lo sfondo di un determinato colore. Ad esempio: ho una colonna di date, solo alcune hanno uno sfondo colorato. Vorrei trovare il modo di ottenere la data più recente tra le date con lo sfondo colorato. Grazie
Ottima soluzione! Funziona splendidamente, la cercavo da tempo. Spero di essere riuscito pure a salvare come componente aggiuntivo. Grazie della condivisione
salve, ho un calendario in colonna B, orario in colonna C ,idem in colonna D in colonna E la diferenza tra c e d, il tutto con formatazione condizionale per evidenziare le domeniche (=GIORNO.SETTIMANA($B2)=1) , come posso sommare le cella della colonna E tralasciando le celle rispondenti alle domeniche e giorni festivi che rientreranno nei vari mesi
Buongiorno, premetto che ho utilizzato più volte la funzione FormatSum senza problema alcuno con molta soddisfazione per i risultati. Su un foglio di lavoro gestionale (lavoro per un ente locale) salvo il foglio excel con attivazione macro, inserisco il codice VBA sul modulo 1 (non ci sono altri codici VBA sullo stesso file) inserisco la formula FormatSum sotto ogni colonna ed i risultati sono correttissimi. Se chiudo e riapro il file nelle celle sotto ogni colonna trovo #NOME? e nella scheda Sviluppo trovo attivata la Modalità progettazione. se cerco di toglierla e in una finestra ottengo il messaggio “A causa delle impostazioni di protezione le macro sono state disattivate. Per eseguirle è necessario aprire la cartella di lavoro, quindi scegliere di attivarle. Nel Centro protezione -> Impostazioni macro è spuntata la casella “Attiva tutte le macro (scelta non consigliata …) e nella scelta Impostazioni macro sviluppatori è spuntata l’unica casella “Considera attendibile l’accesso al modello a oggetti dei progetti VBA”. Uso Excel 2007 Qualcuno è in grado di aiutarmi? Grazie e cordiali saluti a tutti