Prendiamo un intervallo di celle in Excel e immaginiamo di voler sommare o contare celle colorate, ovvero solo di quelle celle che presentano o nello sfondo o nel carattere, un particolare colore.
Con la procedura seguente vediamo come sia possibile.
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 relativo alla funzione desiderata:
Per contare le celle di un intervallo in base al colore dello sfondo o del carattere
Function CountByColor(InRange As Range, _
WhatColorIndex As Integer, _
Optional OfText As Boolean = False) As Long
'
' This function return the number of cells in InRange with
' a background color, or if OfText is True a font color,
' equal to WhatColorIndex.
'
Dim Rng As Range
Application.Volatile True
For Each Rng In InRange.Cells
If OfText = True Then
CountByColor = CountByColor - _
(Rng.Font.ColorIndex = WhatColorIndex)
Else
CountByColor = CountByColor - _
(Rng.Interior.ColorIndex = WhatColorIndex)
End If
Next Rng
End Function
Chiudere la finestra e ritornare in Excel. La funzione che deve essere inserita per contare le celle secondo un colore è la seguente:
=COUNTBYCOLOR(INTERVALLO,COLORE,TIPO COLORE)
In cui:
INTERVALLO: L’intervallo delle celle che verranno analizzate
COLORE: Il numero del colore relativo alle effettive celle da contare
TIPO COLORE: VERO per indicare che il colore è riferito al testo – FALSO per indicare che il colore è riferito allo sfondo
Esempio: =COUNTBYCOLOR(A10:B30,3,FALSO)
Conta, nell’intervallo A10:B30, le celle che hanno lo sfondo rosso
Il numero da inserire nella formula, relativo al colore da analizzare, lo si può dedurre dalla tabella in fondo (numeri da 1 a 56)
Per sommare le celle di un intervallo in base al colore dello sfondo o del carattere
Function SumByColor(InRange As Range, WhatColorIndex As Integer, _
Optional OfText As Boolean = False) As Double
'
' This function return the SUM of the values of cells in
' InRange with a background color, or if OfText is True a
' font color, equal to WhatColorIndex.
'
Dim Rng As Range
Dim OK As Boolean
Application.Volatile True
For Each Rng In InRange.Cells
If OfText = True Then
OK = (Rng.Font.ColorIndex = WhatColorIndex)
Else
OK = (Rng.Interior.ColorIndex = WhatColorIndex)
End If
If OK And IsNumeric(Rng.Value) Then
SumByColor = SumByColor + Rng.Value
End If
Next Rng
End Function
Chiudere la finestra e ritornare in Excel. La funzione che deve essere inserita per sommare le celle secondo un colore è la seguente:
=SUMBYCOLOR(INTERVALLO,COLORE,TIPO COLORE)
In cui:
INTERVALLO: L’intervallo delle celle che verranno analizzate
COLORE: Il numero del colore relativo alle effettive celle da sommare
TIPO COLORE: VERO per indicare che il colore è riferito al testo – FALSO per indicare che il colore è riferito allo sfondo
Esempio: =SUMBYCOLOR(A10:B30,3,FALSO)
Somma, nell’intervallo A10:B30, le celle che hanno lo sfondo rosso
Il numero da inserire nella formula, relativo al colore da analizzare, lo si può dedurre dalla tabella in fondo (numeri da 1 a 56)
Da tener presente, per entrambi i casi, che se il colore nelle celle viene cambiato il calcolo non si aggiorna automaticamente ma sarà necessario aggiornarlo manualmente ad esempio tramite il tasto F9.
P.S.
Nelle funzioni, per delimitare gli argomenti, andrà utilizzato il separatore appropriato (punto e virgola o virgola) a seconda delle impostazioni internazionali utilizzate.
Tabella Colori
Informatics, ski racer, loves travels, Finland, Norway, mountains, squash, photography, Drones and RC Helicopter
RSS - Articoli
grazie per l'aiuto!
aspetto riconoscente gli aggiornamenti
saluti dalla Polonia
Mi chiamo Federico, da Bologna. GRAZIE. funziona a meraviglia. queste prime volte sembreranno sempre una specie di magia …
grazie ancora
SEMPLICEMENTE.. PERFETTO!
GRAZIE MILLE
Ciao.
Ho seguito passo passo le istruzioni (copia e incolla, in buona sostanza) però ottengo sempre l'errore "Rilevato nome non univoco. SumByColor".
Ho visto che si è presentato anche ad altri e che hanno risolto: potete dirmi come?
Anche un aiuto dall'autore sarebbe graditissimo 🙂
Grazie 1000.
Grazie mille per questo modulo, molto utile, solo che è utile per i numeri (e colori) 1,2,3 oltre al 3 il risultato è SEMPRE 0…
ho seguito tutto alla lettera (e nn sn alle prime armi con un computer)
Stefano.
Stefano, probabilmente stai sbagliando qualcosa perchè funziona con tutti i numeri; se vuoi mandami il file per mail che provo a dare un'occhiata.
Prima di tutto grazie per il codice, l'ho provato funziona ma per farlo funzionare, una volta cambiato il colore, non mi basta fare f9 devo rientrare nella formula e fare invio altrimenti non aggiorna. La funzione appare tra le definite dall'utente ma ha questo problema. Dipende da qualche mia impostazione?
…grande..la formula funziona alla grande. Mi riconosce solo il colore di sfondo della cella e non il colore del font. Come devo modificare la formula per farla funzionare con il colore font?
Grazie 1000 x l'aiuto 🙂
@Stex81: per sommare le celle in base al colore del testo devi mettere VERO come ultimo argomento della formula. Se hai problemi mandami il file che provo a controllarlo.
Help me !
mi sento stupido ma copiando tutto il codice (parte rossa e parte blu) nella finestra e poi uscendo con "esci e torna ad Excel", quando copio la formula nella cella che mi interessa, il programma mi restituisce "errore di compilazione: previsto : fine istruzione" e un quadratino azzurro mi si posiziona sul "le" del titolo. Forse dovevo compilare l'istruzione prima di chiudere ? Uso il 2003 su XP.
Grazie 1000
@Gabriele: il codice è quello blu, le righe in rosso sono il titolo per capire cosa fa una e cosa fa l'altra, non le devi copiare! Devi prendere solo la parte blu 😉
Ok, sono un pollo !
Ho cancellato il modulo già registrato, ho copiato solo il blu e poi ho copiato l'istruzione nella cella del foglio Excel COUNTBYCOLOR(F4:F40;3;false) .
Il risultato è che Excel non mi da più errore ma nella cella dove ho inserito la formula compare uno scoraggiante #VALORE!
Questa volta dove ho sbagliato ? Mannaggia a me !
Comunque grazie per il prezioso aiuto. Ciao
Gabriele
Ragazzi ho provato ad inserire la lsita in Execl 2007 ma il risultato è sempre zero….
Anche a me risulta sempre zero vorrei contare nella colonna A di excel tutte le celle colorate in viola (13) ma il risultato è zero anche se uso altri colori.
GRAZIE
@Gabriele: ammesso che tu abbia Excel in Italiano allora hai messo FALSE al posto di FALSO come ultimo argomento della formula.
@AXE e Anonimo: state sbagliando qualcosa o nella creazione del modulo o nella formula; se non riuscite a risolvere mandatemi il file che ve lo sistemo.
Sei un grande!
ho risolto in 5 minuti un problema che da solo non avrei mai risolto: grazie mille
Luca
Ok, ho la conferma che io sono un pollo !
Per fortuna che ci sei tu che sei un GRANDE !
Non so come ringraziarti.
ciao
Gabriele
Grazie mille…
se mi assumono è merito tuo!
@Gabriele: di nulla!
@Lucia: Allora nel caso devi pagare da bere 😉
Innanzi tutto, grazie mille!
Mi puoi suggerire come fare in modo che il ricalcolo della somma sia automatico? ossia, se cambiano i numeri, il ricalcolo avviene correttamente, ma se cambio il colore dello sfondo alle celle dell’intervallo, devo forzare sempre manulamente il ricalcolo, quello automatico non funziona..
Grazie, Elisabetta
@Elisabetta: serve modificare il codice della funzione per far si che diventi automatico il ricalcolo. Non appena ho un attimo di tempo aggiorno la formula.
Intanto grazie mille per il tempo che vorrai dedicarci!
Ciao Andrea,
memore del prezioso aiuto che mi desti con le celle colorate, provo a sottoporti un quesito nuovo.
Si riesce a far disegnare una gaussiana ad Excel ?
Grazie comunque
Gabriele Monciatti
ho provato la fnzione in excel 2003, funziona a meraviglia, purtroppo non funziona su office 2010. Bisogna aggiornare qual cosa??
@ss.gg: probabilmente sbagli qualcosa, funziona perfettamente anche in Office 2010
Buon giorno Andrea, ho riprovato la soluzione, e devo dire che funziona.
Non sono riuschito a salvarla come componente aggiuntivo su office 2010, ma semplicemente un file con attivazione macro.
Grazie.
saluti
Ciao grazie mille per l’ottimo post mi è stato utilissimo vorrei sapere se fosse possibile usarlo anche per celle che subiscono la formattazione condizionale…
ciao grazie!!!
@Mimmo: putroppo no il codice non vale per celle formattate tramite la formattazione condizionale.
ma quello che mi chiedo è: si può fare?
questo codice potrebbe essere adattato?
@Minno: no, questo codice non permette di farlo. In ogni caso puoi sommare o contare le celle basandoti sulla stessa condizione che utilizzi nella formattazione condizionale.
Con excel mi funziona con open office no, perché?
nel caso che le celle colorate da contare, siano colorate a causa di una formattazione condizionale, excel non riesce a contarmele. si può fare qualcosa in merito?
scusa, ho letto ora gli altri post!!
puoi dirmi quali comandi si devono usare per contare le celle basandomi sulla mia formattazione condizionale?
@luca: come ho già scritto sopra questa formula non tiene conto della formattazione condizionale.
Ciao,
Anch’io cerco di dare una mano agli altri nel mio campo, quando posso. Proprio per questo, visto che le istruzioni date su questa pagina web mi hanno risolto un problema, vorrei ringraziare per il know-how che ho potuto utilizzare.
Cordiali saluti / Klaus
Grazie mille funziona a meraviglia.
Ciao Nicola
Thankx!!
AIUTO!Ho un piccolo problema non riesco a fare la somma del colore nero, (l’ho inserito in automatico) che munero devo inserire? il rosso per esempio mi funziona perfettamente
@FRA: il colore devi inserirlo con una formattazione, per il nero devi scegliere il colore nero, non automatico. L’indice di colore nero è 1
Su LibreOffice non funziona (riporta risultati sbagliati). Come si può correggere? Sarei estremamente grato se funzionasse anche con LibreOffice o OpenOffice.
bravo , grazie
andrea 6 un mito, è uno strumento davvero utile, ti sono davvero grata
manca però la ciliegina sulla torta…
…il ricalcolo automatico.
Pensavo si fosse disabilitato da excel per qualche strano motivo
e invece è flaggato bello che buono (infatti per tutti gli altri conteggi funziona).
Andare a cliccare ogni volta f9 per aggiornare il ricalcolo, ogni qual volta si modifica un colore,
è davvero una scocciatura. anche perchè a volte cade in dimendicanza e quanto i calcoli da fare sono tanti si perdono mezz’ore di lavoro a rifarli
ho visto che la @Elisabetta qualche tempo fa ha riscontrato il mio stesso grattacapo e quindi
volevo chiederti se hai risolto questa sorta di “bug” implementandone in qualche modo il comando o con qualche altra tua acuta astuzia 🙂
Spero in un tuo ennesimo colpo di genio
io ci ho smanettato un po’ ma non sono riuscita a trovare la soluzione
ovviamente se la trovo la posterò qui per utilità di tutti, anzi chiedo umilmente a chiunque sia in grado di implementarla di contribuire al meraviglioso servizio reso da Andrea nel suo utile blog
thx
@shining.dreamer: 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 ma va aggiornato manulamente con F9. Se si vuole che venga effettuato il ricalcolo senza utlizzare F9 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 sula 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.
ciao Andrea,
premetto che sono un neofita di excel e sto imparando grazie anche a te.
sto usando l’istruzione COUNTBYCOLOR ma nonostante metta lo sfondo colorato (per esempio rosso -5) mi da sempre 0.
Dove sbaglio?
ciao e complimenti
Gio
@giovanni: così è praticamente impossibile dirti dove sbagli; se mi mandi il file posso guardare dov’è l’errore. In ogni caso se segui passo-passo le istruzioni dovrebbe funzionare.
Ma oltre a contare e sommare è possibile calcolare il valore minimo e massimo delle celle di uno stesso colore?
@fabio: si, è possibile, trovi la spiegazione qui: https://www.andreaperotti.ch/2012/11/visualizzare-il-valore-minimo-o-massimo-di-un-intervalo-di-celle-di-excel-in-base-al-colore/
GRAZIE!!!
Mi ha aperto un mondo di nuove possibilità!!!
Ora però mi manca il prossimo step: È possibile filtrare le celle di un certo colore??
Questo sarebbe LA soluzione a parecchi problemi…
@Numa: Excel 2010 o 2013 permette di filtrare le celle in base al colore; altrimenti puoi seguire questo metodo: https://www.andreaperotti.ch/2012/11/filtrare-le-celle-di-excel-in-base-al-colore/
grazie al tuo programma sono riuscito a fare molte cose in più e per questo ti ringrazio, tuttavia quando chiudo il file e poi lo riapro non mi funziona più, mi da nome?. Devo per caso salvare in qualche modo particolare?
grazie mille
claudio
@Claudio: puoi creare un componente aggiuntivo in modo tale che la funzione sia disponibile quando avvi Excel; trovi le istruzioni qui: https://www.andreaperotti.ch/2007/05/creare-un-componente-aggiuntivo-add-ins-per-excel/
Fantastico e grazie………anche a me è stata molto utile, ma con la formula inserita in una cella se cambio la colorazione di una cella devo rientrare sulla cella ella formula e riaggiornarla…..non la fa in tempo reale……sbaglio io qualcosa??? Grazie ancora.
Io non ci riesco.. mi viene fuori errore.. uso excel in inglese…
e la formula che inserisco è =SUMBYCOLOR(C2:C76,3,FALSE)
…che sbaglio?
@erika: così è un po’ difficile capire cosa tu stia sbagliando. Che impostazioni internazionali stai usando? Non è che come separatore degli argomenti al posto della virgola devi mettere il punto e virgola?
Chiaro e semplice anche per un totale profano come me.
Grazie mille
io uso da tempo questa funzione ed è OK…
vi è la possibilità di modificarla per ottenere lo stesso risultato ma con la seguente differenza:
le celle colorate da analizzare sono in A mentre i corrispettivi importi da sommare sono in colonna B. Quindi un doppio Range…
PF è possibile?
Grazie e saluti…
PS: ovviamente è un caso dove la colonna degli importi non deve essere settata con i colori …
Grazie mille, funziona molto bene. Ora ho un solo problema: per farla aggiornare in tempo reale come posso fare? Cambiando i colori della cella la formula non cambia senza aprirla e senza ripremere invio. Neanche F9 funziona. Per e sarebbe indispensabile perché ho una tabella con circa 50 formule per contare il colore delle celle e aggiornarle manualmente tutte diventa un po’ complicato…
Grazie mille per il tuo lavoro
Rettifico il commento inviato. Funziona tutto,anche F9. Houl altra domanda da porti: se ho celle dello stesso colore contenenti un carattere diverso, ad es. due celle gialle che contengono una la lettera M e una la lettera C, è possibile contare le celle di colore giallo che contengono solo la lettera c? perché ho trovato delle guide ma con i valori invece che con i caratteri. Inoltre, è possibile fare questo in base al carattere maiuscolo o minuscolo? Cioé, due celle gialle una contenente il carattere M e una il carattere m. Sommare le celle gialle con carattere minuscolo m è possibile o non fa distinzioni tra maiuscolo e minuscolo?
Grazie
Ciao Andrea, grazie mille per l’utilissima funzione.
Purtroppo però non riesco a visualizzare la tabella con il riferimento colori/codice, è un problema del mio Pc?
grazie mille
daniele
@daniele: sì è un problema di visualizzazione del tuo pc. In alternativa puoi scaricare la tabella tramite questo link: https://app.box.com/s/ko4beisdz3hpno2f3pd0
@luca: questo dovrebbe fare al caso tuo: https://www.andreaperotti.ch/2015/05/contare-in-excel-2010-celle-colorate-che-contengono-uno-specifico-testo-rispettando-le-maiuscole-o-minuscole/
Buongiorno,
trovo il suo lavoro molto utile ed interessante, ma non riesco a scaricare la tabella dei colori: mi restituisce l’avviso di file o cartella condivisa rimossa. È possibile trovarla altrove?
Grazie mille.
@luca vian: è un problema di box.net dove sono caricati i files; in attesa che risolvano può mandarmi una mail così glielo invio.
Buongiorno, io ho inserito la formula seguendo le istruzioni e funziona, ma ogni volta che, in qualsiasi file Excel, provo a cambiare qualcosa, anche solo a spostarmi da una cella all’altra, mi si blocca e mi dice che il programma non risponde. Come posso risolvere?
Grazie
Ciao Andrea, questa formula è veramente molto utile e la tua spiegazione è molto chiara.
Io avrei bisogno di aggiungere un ulteriore criterio per la somma oltre al colore. Vorrei sommare i valori di una matrice in base al colore del carattere e in base al contenuto delle celle di un’altra colonna. Ho provato a combinare le funzioni sumbycolor e somma.se ma non ne ho ricavato nulla.
Se potessi darmi un suggerimento te ne sarei grata!
Grazie e buona giornata!
Marghe
ciao, sembrerò idiota ma su excel 2011 non ci riesco ad inserire sul vba.
Cioè sembra scritto ma non effettua nulla sul foglio elettronico.
potresti rifare il tutorial ? magari su youtube….
grazie mille comunque lo stesso.
GL
@gianluca: questa guida si riferisce ad Excel per Windows, Excel 2011 è la vecchia versione per Mac, purtroppo non ce l’ho più installata e quindi non posso dirti con certezza se funziona o meno. Ti posso invece dire che con Excel 2016 per Mac funziona.
Ciao, ti ringrazio perche’ la macro mi e’ molto utile.
Purtroppo non riesco a farla aggiornare automaticamente (ho gia’ controllato in formule/opzioni di calcolo ed e’ settato in automatico, inoltre la cella contenente la formula e’ in formato “generale”. L’ho messa come “numero” ma ancora non aggiorna in automatico. O meglio, se copio formato con il pennellino – colore copia formato colore- si aggiorna in automatico. Invece, se coloro una cella, per farla contare dalla formula, devo o cliccare sulla cella con la formula, o cliccare formule/calcola foglio). Sai aiutarmi? Grazie e ciao.
@elena: il ricalcolo automatico in un foglio Excel avviene quando inserisci o cambi il dato in una cella qualsiasi o quando modifichi la formattazione tramite il copia formato. Se invece cambi il formato di una o più celle non viene effettuato il ricalcolo automatico del foglio.\nQuindi se vuoi che venga effettuato il ricalcolo automatico del foglio in base al cambiamento della formattazione o copi il formato oppure lo abiliti come descritto qui: https://www.andreaperotti.ch/2013/03/ricalcolo-automatico-celle-excel/
Ciao,
scusa se ti disturbo, io utilizzo il =SUMBYCOLOR(D3:D6;3;VERO)ma, il risultato è pari a 0. Riusciresti ad aiutarmi?
Grazie mille
Marco
P.s per la funzione incollo esattamente quella riportata da te.
é il sito più preciso e chiaro che spiega questa situazione.
Dopo la funzione in visual basic, applicando la formula conteggio mi da messaggio di errore in quanto utilizzati simboli non corretti.
@pier luigi: dopo aver copiato il codice controlla che nel fare copia/incolla non siano stati formattati in modo non corretto i trattini, apostrofi, ecc. Nel caso riscrivili con la tastiera.
Post molto utile ma devo esporre un’ulteriore questione:
le celle che vorrei contare sono colorate con la formattazione condizionale, a seconda dei valori inseriti le celle assumono un colore diverso.
Ho provato ad inserire la formula che hai descritto ma funziona solo se imposto il colore a mano(con il secchiello per intenderci), c’è un modo per risolvere questo problema? Spero che negli insondabili abissi di funzioni di Excel ci sia una formula anche per questo..! Grazie infinite!
Grazie infinite per la soluzione, funziona, mi associo alla domanda di Alessandra per quanto riguarda le celle colorate con la formattazione condizionale, c’è modo di mantenere i colori già formattati e inserire i valori? ancora grazie.
Salve, ho un excel 2003 ma non sò da dove partire per contare le celle colorate
Ciao,
ho la versione 15.28 di Excel per mac.
Ho seguito alla lettera ma mi da il seguente errore:
Per informazioni utili sull’immissione di funzioni, fare clic su OK, quindi scegliere Funzione dal menu Inserisci.
o Se non si desidera immettere una formula, non utilizzare il segno di uguale (=) o di sottrazione (-) oppure anteporre a esso un apostrofo (‘).
Sapreste aiutarmi? Grazie mille
@Valentina: se hai seguito alla lettera deve funzionare, probabilmente stai sbagliando qualcosa nella sintassi della funzione. Dire cosa senza sapere cos’hai fatto diventa difficile 😉
Ciao, il calcolo per colore nel foglio va bene ma come applicarlo a piu’ fogli della stessa cartella?
Grazie.
Ciao Andrea, volevo sapere se era possibile contare le celle che hanno lo stesso bordo e dello stesso colore.
esempio:
A1= bordo colorato rosso sx
B1= bordo colorato rosso sx
C1= bordo colorato rosso dx
D1= bordo colorato rosso dx
E1= bordo colorato rosso dx
F1= bordo colorato rosso ALTO
G1= bordo colorato rosso Basso
il risultato dovrebbe dare 2 per le celle con bordo a sx
e 3 per le celle con Bordo dx 1 per le celle con Bordo Basso 1 per le celle con Bordo Basso.
Grazie in anticipo
Roby
Ciao Andrea.
Volevo sapere se conosci un metodo per effettuare il conteggio delle colonne che sono colorate tutte di giallo (per esempio).
Tipo colonna A1 tutta verde (NO) A5 tutta gialla(1) etc. etc.
Grazie
buongiorno,
ho aggiunto il codice e mi riconosce la funzione, ma compare sempre 0 come risultato della formula
Ciao volevo ringraziarti per l’aiuto, la somma per colore delle celle funziona a meraviglia.
Salve ho provato la funzione di countbycolor e funziona perfettamente, siccome mi dà problema al salvataggio del lavoro come devo fare?? Uso un office excel 2019
Buongiorno, la funzione è perfetta, non avevo dubbi ma non so come salvare. Chiudo Visual basic, chiudo il file e salvo come file con attivazione macro di excel, ma quando lo riapro le celle di sumbycolor non ci sono più
@Maurizio: la puoi salvare come componente aggiuntivo e poi attivarlo; qui trovi i passaggi: https://www.andreaperotti.ch/2007/05/creare-un-componente-aggiuntivo-add-ins-per-excel/
Buongiorno Andrea, grazie per il codice e per la spiegazione. CountByColor: è possibile usarlo anche per celle aggregate sia per colonne che per righe? La macro funziona benissimo ma mi conta tutte le celle come se fossero separate.