Räkna och summera celler med färg

Många önskar summera celler med en viss färg i Excel. Man vill markera celler och fylla dem med olika färger för att sedan kunna summera alla celler med en viss färg.

Ibland vill vi räkna antalet celler med en viss färg eller summera beloppen i celler med en viss färg.

Excel kan inte beräkna celler beroende på cellfärg, via någon funktion men det går genom att programmerar i VBA (Visual Basic for Applications). Se detta tips https://www.excelbrevet.se/summera-celler-efter-farg/.

Vill du kunna summera celler med en viss färg, utan att använda VBA kod, så går det också.

Om du gör tvärtom och låter Excel färga cellerna och använder en hjälpkolumn, som sedan kan döljas.

Istället för att själv färga cellerna (med risken att du missar att byta färg på celler, när värden ändras) så går det att be Excel att färga celler, med hjälp av Villkorsstyrd formatering [Conditional Formatting].

Hämta exempelfilen här.

Skapa Hjälpkolumn

I denna planering över veckoarbetstid vill vi färga celler med en arbetstid över 8 timmar i grönt och dagar med färre arbetstimmar än 8 med rött.

För detta har vi har skapat en hjälpkolumn.

Innehållet i cellerna hjälpkolumnen G går att fylla i förhand, använda funktionen Dataverifiering [Data Validation] för att välja innehåll från en rullgardinsmeny eller skapa en OM [IF] formel så Excel själv fyller i rätt innehåll.

I detta exempel ha vi skapat en OM [IF] formel som håller koll på övertidstimmarna och resulterar i texterna Normal, Under eller Över då arbetstiden är lika med 8 timmar, har mindre eller mer arbetstimmar om dagen. (8 timmar är lika med 1 dygn, delat på 24 timmar, gånger 8).

Vi använde denna formel:

=OM(F4=(1/24)*8;”Normal”;OM(F4<(1/24)*8;”Under”;”Över”))

=IF(F4=(1/24)*8;”Normal”;IF(F4<(1/24)*8;”Under”;”Över”))

Läs mer om funktionen OM [IF] i detta tips: https://www.excelbrevet.se/om-funktionen/

Mer om funktionen Dataverifiering [Data Validation] finns i självstudiekursen Excel Fördjupning https://www.officekurs.se/alla-kurser/excel-fordjupning/

Skapa regel för Villkorsstyrdformatering

Vi börjar med att skapa en regel som färgar celler röda, om arbetstiden understiger 8 timmar i F kolumnen med hjälp av värdena i hjälpkolumnen G.

1. Markera alla de celler som ska färgas, i kolumn F.
2. Välj Ny regel [New Rule] under Villkorsstyrd formatering [Conditional Formatting] på menyfliken Start [Start].

1. Välj Bestäm vilka celler som ska formateras genom ett använda en formel [Use a formula to determine which cell to format].

1. Klicka på en cell i hjälpkolumnen, som innehåller ett värde som avgör om cellen ska färgas eller inte.
2. Fyll i så formeln blir:

=$G$4=”Under”

1. Ta bort $-tecknet framför radsiffran, så formeln ser ut så här:

=$G4=”Under”
Vi vill ju att denna regel ska fungera på alla raderna i kolumn G, inte bara på rad 4, i detta exempel.

1. Klicka på Formatera [Format].
2. Välj en önskad fyllningsfärg.
3. Klicka på OK två gånger.

1. Nu färgas celler röda, om arbetstiden understiger 8 timmar.
2. Upprepa steg 1-9 och skapa en regel som färgar celler gröna, om arbetstiden överstiger 8 timmar.
3. Använd formeln:

=$G4=”Över”

Var noga och se till att ta bort $-tecknet framför radsiffran.

Tips! Använd kortkommandot F2 när du redigerar formler i dialogrutor. Normalt när du använder pil-tangenterna, flyttar sig markören mellan celler på kalkylarket, men efter du tryckt på tangenten F2, i dialogrutor, kan du gå med pliarna mellan bokstäverna och siffrorna i formler.

Nu syns det tydligt vilka dagar som har mer eller minde än 8 arbetstimmar.

Räkna antalet ”färgade” celler

Excel kan inte känna av fyllnadsfärgen i celler för att göra beräkningar men det går räkna antalet celler i hjälpkolumnen G, som styr färgen i kolumnen F istället.

Om du sedan döljer hjälpkolumnen G, ser det ut som Excel räknar celler med en viss färg.

För att räkna hur många celler som innehåller ett visst villkor, i detta fall en text i hjälpkolumnen G, kan du använda funktionen ANTAL.OM [COUNTIF].

I cellen C18 fyller vi i formeln:
=ANTAL.OM(G4:G13;”Normal”)

=COUNTIF(G4:G13;”Normal”)

Skriv in formeln förhand eller använd funktionsguiden.

Ange cellerna, i hjälpkolumnen G, som området där Excel ska räkna antalet förekomster och fyll i texten ”Normal” (inom ”citationstecken”) som villkoret som ska vara uppfyllt.

Skapa även formler som räknar antal dagar med övertid och antalet dagar med arbetstimmar som understiger 8.

=ANTAL.OM(G4:G13;”Över”)

=COUNTIF(G4:G13;”Över”)

=ANTAL.OM(G4:G13;”Under”)

=COUNTIF(G4:G13;”Under”)

Tips! Ange området $G$4:$G$13 med absoluta referenser med $-tecken om du vill kunna kopiera formlen till övriga celler, utan att behöva fylla i samma område igen.

Summera ”färgade” celler

För att räkna ut summan av värden i cellerna i kolumn F, med funktionen SUMMA.OM [SUMIF] kan villkoret finnas i en annan kolumn, i detta fall en text i hjälpkolumnen G.

I cellen D18 fyller vi i formeln:
=SUMMA.OM(G4:G13;”Normal”;F4:F13)

=SUMIF(G4:G13;”Normal”;F4:F13)

Skriv in formeln förhand eller använd funktionsguiden.

Ange cellerna hjälpkolumnen G som området där Excel ska hitta förekomster av villkoret ”Normal” och ange cellerna i kolumnen F, som det område som ska beräknas.

Vi skapar även formler som summerar övertid och arbetstimmar som understiger 8.

=SUMMA.OM(G4:G13;”Över”;F4:F13)

=SUMIF(G4:G13;”Över”;F4:F13)

=SUMMA.OM(G4:G13;”Under”;F4:F13)

=SUMIF(G4:G13;”Under”;F4:F13)

Mer tips

• Genom att låta Excel färga celler med hjälp av Villkorsstyrd formatering [Conditional Formatting], istället för att färga cellerna själv, minimerar du risken att missa att byta färg på celler, när värden ändras.
• Genom att använda en hjälpkolumn, som till exempel via en OM [IF] funktion skapar cellvärden som Excel kan använda i formler, minimerar du risken att du själv inte hittar alla celler som ska färgas och beräknas.
• Hjälpkolumnen kan du sedan dölja och det kommer att se ut som Excel räknar celler som innehåller färger.

Extra tips – Formatera tid

För att summan av timmarna ska visas korrekt, behöver celler som innehåller klockslag, ha ett visningsformat som tillåter visning av timmar som överstiger 24.

• Välj tidsformatet 37:30:55 i dialogrutan för Formatera celler [Format Cells].