[Excel] Waardes van gekleurde tekst in cellen optellen

[Anoniem: 797949]

Ik heb in Excel iets gemaakt zodat ik kan zien welke facturen nog betaald moeten worden. Ik geef het betreffende bedrag dat nog betaald moet worden in de cel een rode tekstkleur. Nu heb ik al een macro gevonden die alle cellen met de rode tekstkleur bij elkaar optelt daarmee zie ik dus het aantal openstaande facturen, echter wil ik ook het totaalbedrag weten van deze openstaande facturen. Dus alle waardes in de cellen met rode tekst bij elkaar opgeteld. De macro die ik heb gevonden doet zich niet automatisch aanpassen, als ik een waarde aanpas moet ik de formule selecteren in de cel en dan pas telt door.
Gevonden Macro:

Function KleurAantal(c As Range, b As Range) As Long
Dim x As Range
For Each x In b
If x.Font.Color = c.Font.Color And Not IsEmpty(x) Then
KleurAantal = KleurAantal + 1
End If
Next x
End Function

Formule die ik gebruik:
=kleuraantal(E4;$E$3:$E$22)

Is hier een formule/macro voor die dit kan?
Ik ben geen excel expert dus graag een gemakkelijke uitleg...
Alvast bedankt voor de reacties.

[ Voor 3% gewijzigd door Anoniem: 797949 op 27-07-2016 19:35 ]

[Fish]

lijkt me dat je dan even een andere eigenschap moet aanropen van het veld

.Value en dat optellen

e.g.

Function Dinges(c As Range, b As Range) As Long
Dim x As Range
For Each x In b
If x.Font.Color = c.Font.Color And Not IsEmpty(x) Then
dinges = dinges+ x.value
End If
Next x
End Function

disclaimer: Ongetest hoor

[Anoniem: 797949]

Nee helaas...

[chengbondkwok]

Werken met filters? Je kan volgens mij in Excel filteren op tekstkleur (en celkleur), en dan kan je de optelsom maken. Tenzij je dit vaak moet doen, dan is het minder handig.

[ Voor 19% gewijzigd door chengbondkwok op 27-07-2016 20:17 ]

[Anoniem: 797949]

Dan zet hij inderdaad alle gekleurde tekst onder elkaar, dat is eigenlijk niet wat ik wil. Ik wil rijen niet veranderen wat dan wel gebeurt. Los hier van telt die nog niet automatisch op

[chengbondkwok]

Anoniem: 797949 schreef op woensdag 27 juli 2016 @ 20:22:
Dan zet hij inderdaad alle gekleurde tekst onder elkaar, dat is eigenlijk niet wat ik wil. Ik wil rijen niet veranderen wat dan wel gebeurt. Los hier van telt die nog niet automatisch op

Volgens mij zit in dit linkje de oplossing die je zoekt:
https://www.ablebits.com/...ount-sort-by-color-excel/

En dan het stukje code over SumCellsByFontColor

[Anoniem: 11193]

Zelf zou ik niet moeilijk doen met de kleuren herkennen.

Alternatief is een kolom toevoegen met betaald 'ja/nee'.

Dmv consitional formatting kun je de cel kleuren en dmv countifs en sumifs de boel eenvoudig (op)tellen.

Blijf je ook helemaal vrij van macro's.

[ Voor 11% gewijzigd door Anoniem: 11193 op 27-07-2016 20:32 ]

[The_Vice]

chengbondkwok schreef op woensdag 27 juli 2016 @ 20:27:[...]
Volgens mij zit in dit linkje de oplossing die je zoekt:
......

Beetje lang, lukt mij met dit, als je standaard rood gebruikt:

code:

Public Function KleurSom(RangeWaarden As Range) As Double
'(return as double ipv long om ook achter de komma te tellen)
Dim x As Range
Dim xcol As Variant
Dim ccol As Variant

For Each x In RangeWaarden
 xcol = CLng(x.Font.ColorIndex)
 ccol = 3 'colorindex rood = 3
    If xcol = ccol Then
        KleurSom = KleurSom + x.Value2
    End If
Next x
End Function

Maar ik zou het ook liever doen in de richting zoals Roys het zegt, kolom [betaald] erachter met Ja/Nee. Alleen dan niet moeilijk doen met sumifs etc. Maar maak een pivottable aan op basis van je brontabel om een overzicht te genereren.

Als je hoofdtabel een beetje netjes is opgezet met kolomnamen is dit uiteindelijk veel flexibeler om overzichten mee te maken.

[ Voor 22% gewijzigd door The_Vice op 27-07-2016 20:38 ]

[Anoniem: 797949]

Moet ik dan onderaan nog een formule plaatsen?

[The_Vice]

Anoniem: 797949 schreef op woensdag 27 juli 2016 @ 20:37:
Moet ik dan onderaan nog een formule plaatsen?

Ja,
je formule zal onderaan in bijv. Cell C30 worden: "=KleurSom(C1:C29)" als C1:C29 de (gekleurde) cellen bevat.

[Anoniem: 797949]

Super! dit lijkt te werken alleen als ik het getal in de cel weer zwart maak dan blijft de waarde in de cel waar de formule staat ongewijzigd. Waar ligt dit dan aan?

[TRON]

Wellicht moet de cel dan opnieuw berekend worden. Druk eens op F9?

Anders kan je het beter oplossen zonder macro's zoals roys al aangeeft.

Dat kan met de functie SOM.ALS()

[The_Vice]

Anoniem: 797949 schreef op woensdag 27 juli 2016 @ 20:46:
Super! dit lijkt te werken alleen als ik het getal in de cel weer zwart maak dan blijft de waarde in de cel waar de formule staat ongewijzigd. Waar ligt dit dan aan?

Ik denk omdat Functies alleen bij wijzigingen van data opnieuw hun berekening doen.

Dat geeft meteen één van de manco's aan bij deze manier van werken. Een ander is bijvoorbeeld als je je gegevens ergens heen kopieert, dat opmaak vaak niet wordt meegenomen.

F9 zal niet werken (in ieder geval bij mij niet) als er geen data is gewijzigd.

"F2+ enter" in de cell met formule doet hem zoiezo updaten (wel wat lompe methode)

[ Voor 6% gewijzigd door The_Vice op 27-07-2016 20:57 ]

[Lustucru]

Psst...
Lustucru in "excel cellen op kleur sorteren/optellen"
Omdat hier een referentiecel wordt meegegeven is hij bruikbaar voor elke achtergrondkleur.

Lustucru in "excel cellen op kleur sorteren/optellen"

The_Vice schreef op woensdag 27 juli 2016 @ 20:53:
[...]
F9 zal niet werken (in ieder geval bij mij niet) als er geen data is gewijzigd.

vandaar dus die regel volatile: dan werkt F9 wel.

[ Voor 49% gewijzigd door Lustucru op 27-07-2016 21:08 ]

[Anoniem: 797949]

Vooralsnog de macro gebruikt van The_Vice. alleen als de tekst terug zwart gemaakt wordt werkt de formule niet tenzij ik in de cel dubbelklik en dan weer op enter duw telt die wel goed op. Hier kan ik wel mee leven..

Iedereen bedankt voor het meedenken!!

[The_Vice]

Anoniem: 797949 schreef op woensdag 27 juli 2016 @ 21:03:
Vooralsnog de macro [_Functie ] gebruikt van The_Vice. alleen als de tekst terug zwart gemaakt wordt werkt de formule niet tenzij ik in de cel dubbelklik en dan weer op enter duw telt die wel goed op. Hier kan ik wel mee leven..

Iedereen bedankt voor het meedenken!!

code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Range("C30").Formula = Range("C30").Formula
End Sub

Als je dit aan je sheet code toevoegt (niet in de module van je functie) dan wordt de formule van je cell elke keer overschreven (en herberekent zich dan dus daarom) met zichzelf als je je selectie wijzigt (lees pijltjes toets e.d.) Ook wat lomp omdat C30 er hardt instaat, maar wel doeltreffend.

[Anoniem: 797949]

Bedoel je dat ik die onder aan de bestaande macro moet toevoegen?

[The_Vice]

Dat moet in de code van het sheet zelf, maar de .volatile methode zoals in het voorbeeld van Lustucru lijkt me een elegantere oplossing.

_{[die gaat The_Vice proberen te onthouden,om toe te passen mocht de situatie zich eens voor doen]}

[Anoniem: 797949]

Okay, ik krijg vierkante ogen. Genoeg om e.e.a. uit te gaan proberen lijkt me.

Bedankt allen!

[TripleZ]

Ik heb de code van het volgende artikel gebruikt: https://www.ablebits.com/...count-sum-by-color-excel/

Ik heb de formule gebruikt van het kopje 'How to count by color and sum by color in an Excel worksheet'.

De formule werkt gewoon. Ik sla het vervolgens op als .xlsm (met macro). Na het openen van het bestand zie ik gewoon het resultaat staan van de vorige kleurtelling. Echter als ik een nieuwe formule van '.CountCellsByColor' of 'SumCellsByColor' intyp lukt niet meer. De uitkomsten worden vervolgens vermeld als #NAAM?

[breew]

TripleZ schreef op donderdag 11 januari 2018 @ 10:35:
Ik heb de code van het volgende artikel gebruikt: https://www.ablebits.com/...count-sum-by-color-excel/

Ik heb de formule gebruikt van het kopje 'How to count by color and sum by color in an Excel worksheet'.

De formule werkt gewoon. Ik sla het vervolgens op als .xlsm (met macro). Na het openen van het bestand zie ik gewoon het resultaat staan van de vorige kleurtelling. Echter als ik een nieuwe formule van '.CountCellsByColor' of 'SumCellsByColor' intyp lukt niet meer. De uitkomsten worden vervolgens vermeld als #NAAM?

Waarom schop je een oud draadje, met een reeds beantwoorde vraag, weer omhoog met een nieuw probleem? Beter maak je even je eigen topic.

[Lustucru]

Helemaal waar, en toch zien we daar @breew graag een topicreport voor in plaats van dat leden van de site elkaar de maat nemen. Dat kan namelijk vrij makkelijk ontsporen.

Ondertussen gaat hier een hangslot op.