Excel: meerdere voorwaarden tegelijk tellen

[Merel Visschers]

Graag zou ik in excel binnen een bepaalde groep cellen het aantal cellen met een bepaalde letter en bepaalde tekstkleur tellen.
Afzonderlijk lukt het middels de volgende formules:
- aantal i met =SOM(ALS(K34:K201="i";1;0))
- aantal cellen met rode tekstinhoud met =TelKleuren($B$30;K34:K201)

Maar als ik nou het aantal cellen met een rode (zelfde tekstkleur als in B30) i wil tellen, hoe combineer ik dan bovenstaande formules? Ik heb het idee dat het moet kunnen, maar kan niet de juiste volgorde/tekens voor het combineren vinden....

Alvast ontzettend bedankt!

[dix-neuf]

Voor Telkleuren(...) zul je een vba-functie gebruiken, want standaard zit zo'n functie niet in Excel.
Als je de code daarvan hier plaatst, dan kunnen we bekijken hoe het aantal cellen met een i daar kan worden ingebouwd. Niet duidelijk is wat precies moet worden opgeteld: de cellen met rode tekst én die tekst moet de letter i zijn, of de cellen met een rode tekstkleur + de cellen met een i (ongeacht of de tekstkleur daarvan rood is of niet). Ook is niet duidelijk wat je bedoelt met cellen met een i: cellen met alleen een i, of cellen met een tekst waarin de i voor komt.

[Merel Visschers]

Sorry voor de onduidelijkheid.
Ik zou graag willen dat in een bepaald gebied het aantal letters/codes in een bepaalde kleur geteld worden.
Bijvoorbeeld het aantal rode i's. Maar dat zou ook het aantal groene i's of paarse aa's of zwarte zv kunnen zijn (maar ik neem aan dat dat eenvoudig te veranderen is.
De code die ik gebruik om kleuren te tellen komt ook van een excel forum (waar iemand ook zo vriendelijk was uit te leggen in Jip en Janneke taal hoe je die code dan in excel geplakt krijgt, want ik weet helemaal niets van vbd....

Ik heb toen volgens mij het volgende volgens instructie ergens in geplakt:

code:

Function TelKleuren(ByRef c1 As Range, ByRef c2 As Range, Optional bTekst As Boolean) As Long

  Dim i As Long, c As Range
  Application.Volatile
  
  If c1.Cells.Count <> 1 Then TelKleuren = -1: Exit Function 'signaleer zo dat je referentie niet uit 1 cel bestaat
  
  For Each c In c2.Cells
    If c.Font.Color = c1.Font.Color And (c.Value <> "" And (Not bTekst Or c.Value = c1.Value)) Then TelKleuren = TelKleuren + 1
  Next c

End Function

Het mag ook op een andere manier. Als ik maar van een aantal combinaties van letter(s) met een bepaalde tekstkleur het aantal in een bepaalde tabel kan tellen. Overigens moet dat onafhankelijk zijn van de vulkleur in de cel.

Ben ik zo nog informatie vergeten (anders dan: ik snap dus niets van vba, maar als me duidelijk uitgelegd wordt hoe ik het moet doen krijg ik het er wel in....)

Wederom alvast ontzettend bedankt!

[ Voor 0% gewijzigd door Lustucru op 17-09-2018 17:06 ]

[dix-neuf]

Daarvoor hoeft het voorbeeld van Microsoft op deze webpagina: https://tinyurl.com/yazs3bkh maar een beetje te worden aangepast. Ik gebruik hier F1 als referentiecel, die cel stel je in met de gewenste tekstkleur. Je kunt dan in elk willekeurig bereik het aantal cellen berekenen dat (alleen) de letter i bevat in de tekstkleur van F1; bv: =Teltekstkleur(A1:A20;F1)

code:

Function Teltekstkleur(range_data As Range, criteria As Range) As Long
Application.Volatile
    Dim datax As Range
    Dim xcolor As Long
xcolor = criteria.Font.Color
For Each datax In range_data
    If datax.Font.Color = xcolor And datax.Value = "i" Then
        Teltekstkleur = Teltekstkleur + 1
    End If
Next datax
End Function

[Dido]

Wat gebeurt er als je

code:

=TelKleuren($B$30;K34:K201)

verandert in

code:

=TelKleuren($B$30;K34:K201; TRUE)

Het lijkt erop dat als je in cel B30 een rode i zet, je code doet wat je wilt...

[Merel Visschers]

Dat van Dido (met true) werkt niet. Met =TelKleuren(K34:K201;$B$30 krijg ik ongeact wat ik invul waarde -1, dus dat doet ook niet helemaal wat ik wil.
Ik ga even kijken of ik nog weet hoe de code van dix-neuf in te bouwen. Ogenblikje.....

[dix-neuf]

@Merel, Neem nou eerst eens de code en het voorbeeld uit mijn bericht hierboven over, dan zie je dat die code en functie werken! Daarna kun je ze dan voor andere bereiken gebruiken.

[Merel Visschers]

Ja, die VBM code werkt. Dan moet ik er dus eentje maken voor elke letter/code waarvan ik wil dat die geteld wordt neem ik aan?! Voor het gemak heb ik de naam van deze veranderd in Teltekstkleuri
Ik zal hem zo proberen aan te passen voor andere letters/lettercombinaties (ik vermoed dat ik daar wel uit ga komen).
Bedankt!

[dix-neuf]

Dan moet ik er dus eentje maken voor elke letter/code waarvan ik wil dat die geteld wordt neem ik aan?

Als je aangeeft wat je precies wil tellen, dan is dat wel in de macro te verwerken. Maar met veel verschillende kleuren en veel verschillende letters zijn er al snel zeer vele combinaties mogelijk.

[ Voor 22% gewijzigd door dix-neuf op 17-09-2018 13:44 ]

[Lustucru]

dix-neuf schreef op maandag 17 september 2018 @ 13:35:
[...]
Als je aangeeft wat je precies wil tellen, dan is dat wel in de macro te verwerken. Maar met veel verschillende kleuren en veel verschillende letters zijn er al snel zeer vele combinaties mogelijk.

Dan maken we hem toch gewoon universeel?

Visual Basic:

Public Function CountEquals(ReferenceCell As Range, Source As Range, Operator As Long)
Dim cell As Range
Dim total As Long
Dim isValid As Long

For Each cell In Source
    isValid = True
    If (Operator And 1) Then isValid = (ReferenceCell.Font.Color = cell.Font.Color)
    If isValid And (Operator And 2) Then isValid = (ReferenceCell.Value = cell.Value)
    If isValid And (Operator And 4) Then isValid = (ReferenceCell.Interior.Color = cell.Interior.Color)
'etc, 'etc.
    If isValid Then total = total + 1
Next cell
    
CountEquals = total
End Function

In dit geval wordt een referentiecel vergeleken met een bereik. Afhankelijk van de operator die je meegeeft worden 0,1,2 of 3 aspecten in ogenschouw genomen.

0=tel gewoon het aantal cellen
1=tel het aantal cellen met dezelfde fontkleur
2=tel het aantal cellen met dezelfde inhoud
3=tel het aantal cellen met dezelfde inhoud en dezelfde kleur
4=tel het aantal cellen met dezelfde achtergrondkleur
5=tel het aantal cellen met dezelfde achtergrondkleur en fontkleur
6=tel het aantal cellen met dezelfde achtergrondkleur en inhoud
7=tel het aantal cellen met dezelfde achtergrondkleur, fontkleur en inhoud

---

offtopic:
kan het wat minder aangebrand? Ik heb wat offtopic posts verwijderd

[Merel Visschers]

Ehm, ik had dus al gepost dat de VMB code van dix-neuf gewoon werkt en daarmee is het probleem opgelost (waarvoor dank)!
Wat Lustucru schrijft vind ik ook erg interessant echter, zoals ik al aangaf ben ik een VMB leek en begrijp ik niet helemaal wat ik precies in de code moet aanpassen om als resultaat de in de post genoemde opties 0 tm 7 te krijgen.... En wat ik dan als formule in de cel moet invullen....

[Lustucru]

Merel Visschers schreef op maandag 17 september 2018 @ 13:11:
Dat van Dido (met true) werkt niet. Met =TelKleuren(K34:K201;$B$30 krijg ik ongeact wat ik invul waarde -1, dus dat doet ook niet helemaal wat ik wil.

Inderdaad, want die functie zal altijd op regel 3 de functie verlaten met -1. Wat jij post is gewoon een buggy stukje code. Maar in de volgende variant doet hij het wel en geef je optioneel mee of de tekst ook in de vergelijking mee moet. En dan is @Dido 's suggestie gewoon correct.

Visual Basic:

Function TelKleuren(ByRef c1 As Range, ByRef c2 As Range, Optional bTekst As Boolean) As Long

  Dim i As Long, c As Range
  Application.Volatile
  
  If c1.Cells.Count <> 1 Then 
     TelKleuren = -1
     Exit Function 'signaleer zo dat je referentie niet uit 1 cel bestaat
  end if
  
  For Each c In c2.Cells
    If (c.Font.Color = c1.Font.Color) And (Not btekst or (c2.value = c1.Value)) Then
           TelKleuren = TelKleuren + 1
    end if
  Next c

End Function

Edit:

Merel Visschers schreef op maandag 17 september 2018 @ 17:04:
Ehm, ik had dus al gepost dat de VMB code van dix-neuf gewoon werkt en daarmee is het probleem opgelost (waarvoor dank)!

Psst, het is VBA

Wat Lustucru schrijft vind ik ook erg interessant echter, zoals ik al aangaf ben ik een VMB leek en begrijp ik niet helemaal wat ik precies in de code moet aanpassen om als resultaat de in de post genoemde opties 0 tm 7 te krijgen.... En wat ik dan als formule in de cel moet invullen....

De verschillende codevarianten zijn hierboven al gegeven. Meer dan dat kant en klaar kun je niet vinden.

[ Voor 27% gewijzigd door Lustucru op 17-09-2018 18:21 ]

[Merel Visschers]

Mijn excuses, voor de late reactie en het gebruiken van verkeerde afkortingen....
Maar ik vrees dat ik het toch nog niet helemaal snap....
Ik heb het volgende geplakt als VBA code:

Public Function CountEquals(ReferenceCell As Range, Source As Range, Operator As Long)
Dim cell As Range
Dim total As Long
Dim isValid As Long

For Each cell In Source
isValid = True
If (Operator And 1) Then isValid = (ReferenceCell.Font.Color = cell.Font.Color)
If isValid And (Operator And 2) Then isValid = (ReferenceCell.Value = cell.Value)
If isValid And (Operator And 4) Then isValid = (ReferenceCell.Interior.Color = cell.Interior.Color)
If isValid Then total = total + 1
Next cell

CountEquals = total
End Function

(sorry, ik weet ook niet hoe je zo'n VBA veld invoegt....)
En dan vervolgens de formule =CountEquals(A5;B5:B13) om te proberen in een cel gezet. Dat werkt niet....
Ik moet iets in die VBA code veranderen (iets met met die cijfers?), maar ik snap niet wat...
(ik zou graag het aantal cellen met dezelfde fontkleur en dezelfde tekst/inhoud als in cel .... (in dit geval A5) tellen). Ik voel me heel dom nu....

[Lustucru]

Beter dom voelen en vragen dan dom blijven.

Als je bi het invoeren op het f(x) tekentje klikt zul je zien dat countequals(refentiecell;telbereik;operator) drie waarden opvraagt en je geeft er maar twee. De laatste parameter 'operator' ontbreekt.

code:

=CountEquals(A5;B5:B13;3)

zou het moeten doen. Het laatste getal (de operator) instrueert de functie wat hij allemaal moet vergelijken.

[ Voor 4% gewijzigd door Lustucru op 27-09-2018 20:27 ]

[Merel Visschers]

Moet ik dan overal waar operator staat "=CountEquals(A5;B5:B13;3)" invullen?