#Excel – Contare e sommare celle in base al colore

User Defined Functions (UDF) e Visual Basic for Applications (VBA)

Autore: Andrea Pacchiarotti
Ultimo aggiornamento: 03 Febbraio 2020
Categoria: Microsoft Office Excel Funzioni

#Excel – Contare e sommare celle in base al colore
#Excel: contare e sommare celle in base al colore

Vediamo come Excel possa contare e sommare le celle in base al colore (a prescindere se esse siano colorate a mano o tramite la formattazione condizionale).
Purtroppo Microsoft Excel non possiede una funzione CONTA.COLORE o SOMMA.COLORE e, per soddisfare la richiesta, è necessario ricorrere alle User Defined Functions (UDF) e al Visual Basic for Applications (VBA), che in questo articolo sono già belle e pronte.

Sommario #Excel: contare e sommare celle in base al colore

Contare o sommare per colore in un foglio Excel

Supponiamo di avere una tabella che, nella colonna Pagamento, mostri tre risposte associate a tre colori (Effettuato è verde, Scaduto da 1 mese è giallo e Scaduto da oltre 1 mese è arancio).

#Excel – Contare e sommare celle in base al colore
Contare o sommare per colore in un foglio #Excel

Si desidera contare le celle in base al colore. Vediamo come fare:

Function ColoreCella(xlIntervallo As Range)
Dim indRiga, indColonna As Long
Dim arRisultati()
Application.Volatile
If xlIntervallo Is Nothing Then
Set xlIntervallo = Application.ThisCell
End If
If xlIntervallo.Count > 1 Then
ReDim arRisultati(1 To xlIntervallo.Rows.Count, 1 To xlIntervallo.Columns.Count)
For indRiga = 1 To xlIntervallo.Rows.Count
For indColonna = 1 To xlIntervallo.Columns.Count
arRisultati(indRiga, indColonna) = xlIntervallo(indRiga, indColonna).Interior.Color
Next
Next
ColoreCella = arRisultati
Else
ColoreCella = xlIntervallo.Interior.Color
End If
End Function

Function ColoreCarattere(xlIntervallo As Range)
Dim indRiga, indColonna As Long
Dim arRisultati()
Application.Volatile
If xlIntervallo Is Nothing Then
Set xlIntervallo = Application.ThisCell
End If
If xlIntervallo.Count > 1 Then
ReDim arRisultati(1 To xlIntervallo.Rows.Count, 1 To xlIntervallo.Columns.Count)
For indRiga = 1 To xlIntervallo.Rows.Count
For indColonna = 1 To xlIntervallo.Columns.Count
arRisultati(indRiga, indColonna) = xlIntervallo(indRiga, indColonna).Font.Color
Next
Next
ColoreCarattere = arRisultati
Else
ColoreCarattere = xlIntervallo.Font.Color
End If
End Function

Function ContaPerColoreCella(rData As Range, cellRefColor As Range) As Long
Dim indRefColor As Long
Dim cellaCorrente As Range
Dim conta As Long
Application.Volatile
conta = 0
indRefColor = cellRefColor.Cells(1, 1).Interior.Color
For Each cellaCorrente In rData
If indRefColor = cellaCorrente.Interior.Color Then
conta = conta + 1
End If
Next cellaCorrente
ContaPerColoreCella = conta
End Function

Function SommaPerColoreCella(rData As Range, cellRefColor As Range)
Dim indRefColor As Long
Dim cellaCorrente As Range
Dim somma
Application.Volatile
somma = 0
indRefColor = cellRefColor.Cells(1, 1).Interior.Color
For Each cellaCorrente In rData
If indRefColor = cellaCorrente.Interior.Color Then
somma = WorksheetFunction.Sum(cellaCorrente, somma)
End If
Next cellaCorrente
SommaPerColoreCella = somma
End Function

Function ContaPerColoreCarattere(rData As Range, cellRefColor As Range) As Long
Dim indRefColor As Long
Dim cellaCorrente As Range
Dim conta As Long
Application.Volatile
conta = 0
indRefColor = cellRefColor.Cells(1, 1).Font.Color
For Each cellaCorrente In rData
If indRefColor = cellaCorrente.Font.Color Then
conta = conta + 1
End If
Next cellaCorrente
ContaPerColoreCarattere = conta
End Function

Function SommaPerColoreCarattere(rData As Range, cellRefColor As Range)
Dim indRefColor As Long
Dim cellaCorrente As Range
Dim somma
Application.Volatile
somma = 0
indRefColor = cellRefColor.Cells(1, 1).Font.Color
For Each cellaCorrente In rData
If indRefColor = cellaCorrente.Font.Color Then
somma = WorksheetFunction.Sum(cellaCorrente, somma)
End If
Next cellaCorrente
SommaPerColoreCarattere = somma
End Function

Ovviamente per contare e sommare per colore carattere, anziché colore cella, basta sostituire nelle formule il termine Cella con Carattere.

Da sottolineare che se successivamente si avesse la necessità di colorare manualmente altre celle dell’intervallo, le funzioni non verrebbero ricalcolate in maniera automatica; è infatti questo il comportamento delle macro di Excel, del VBA e delle User Defined Functions.
Ma non c’è nessun problema perché, per aggiornare somma e conteggio, è sufficiente posizionare il cursore in una cella e premere F2 seguito dal tasto Invio.

Contare o sommare le celle colorate tramite formattazione condizionale

Purtroppo le UDF non funzioneranno correttamente se, per colorare le celle in base ai loro valori, è stata utilizzata la formattazione condizionale del tipo

È però possibile superare questo limite con il VBA, vediamo come:
Sub SommaContaFormCond()
Dim indRefColor As Long
Dim cellaCorrente As Range
Dim conta As Long
Dim somma
Dim contaCelle As Long
Dim indCellaCorrente As Long
conta = 0
somma = 0
contaCelle = Selection.CountLarge
indRefColor = ActiveCell.DisplayFormat.Interior.Color
For indCellaCorrente = 1 To (contaCelle - 1)
If indRefColor = Selection(indCellaCorrente).DisplayFormat.Interior.Color Then
conta = conta + 1
somma = WorksheetFunction.Sum(Selection(indCellaCorrente), somma)
End If
Next
Range("G2").Value = conta
Range("G3").Value = somma
'oppure al posto delle due righe precedenti
MsgBox "Conteggio=" & conta & vbCrLf & "Somma= " & somma, , "Conteggio e Somma"
End Sub

Ecco come utilizzarlo:

Se opterete per le due righe
Range("G2").Value = conta
Range("G3").Value = somma

Otterrete il conteggio e la somma rispettivamente nelle celle G2 e G3 (che ovviamente potrete cambiare a vostro piacimento); se invece sceglierete la riga
MsgBox "Conteggio=" & conta & vbCrLf & "Somma= " & somma, , "Conteggio e Somma"
apparirà una finestra con i risultati del conteggio e della somma.

Scarica l'esempio

Se ti interessa puoi approfondire la formattazione condizionale senza VBA o contare con le funzioni statistiche di Microsoft Excel.

Conclusione

Abbiamo visto un'ottima soluzione per ovviare ad una mancanza di Microsoft Excel, ovvero contare le celle in base al colore. Attraverso il VBA e le UDF siamo stati in grado di inventare delle funzioni ed un metodo che ci ha consentito di migliorare l'applicativo risolvendo un'esigenza lavorativa molto comune.

Per saperne di più su Microsoft Excel potrebbero interessarti questi due libri:


Se vuoi approfondire alcuni dei temi trattati, visita la pagina con le mie pubblicazioni cartacee e online.

Se l'articolo ti è piaciuto, condividilo!

Segui l'hashtag #AndreaPacchiarotti