Tip: andere talen zijn Google-Vertaald. Je kunt het English versie van deze link.
Log in
x
or
x
x
Registreren
x

or

Hoe cellen tellen of optellen op basis van celkleur in Google-spreadsheet?

Het tellen of optellen van celwaarden op basis van een specifieke celachtergrondkleur om het resultaat te krijgen als het volgende screenshot getoond. In dit artikel zal ik ingaan op het oplossen van deze taak in Google sheet en Microsoft Excel.

Tel de celwaarden op basis van de celkleur met script in Google sheet

Som-celwaarden op basis van celkleur met script in Google-spreadsheet

Tel of sum celwaarden op cel kleur met Kutools voor Excel in Microsoft Excel


Celwaarden tellen / optellen op basis van achtergrond, lettertype, voorwaardelijke opmaakkleur in Excel:

Kutools for Excel's Count by Color functie kan u helpen om een ​​aantal berekeningen op basis van het lettertype, de achtergrond of de voorwaardelijke opmaakkleur te krijgen zoals u dat in het Excel-werkblad nodig hebt.

Kutools for Excel: met meer dan 200 handige Excel-add-ins, gratis om zonder beperking in 60-dagen te proberen. Download en gratis proef nu!


Tel de celwaarden op basis van de celkleur met script in Google sheet


Het volgende script kan u helpen om de celwaarden te tellen op basis van de specifieke celkleur, doe dit als volgt:

1. Klikken Gereedschap > Script-editorzie screenshot:

2. Klik in het geopende projectvenster op filet > New > Script bestand om een ​​codevenster te openen, zie screenshot:

3. En voer in het promptvenster een naam in voor deze scriptcode, zie screenshot:

4. Klikken OK en kopieer en plak vervolgens de volgende code om de originele code in de codemodule te vervangen, zie screenshot:

function countColoredCells(countRange,colorRef) {
  var activeRg = SpreadsheetApp.getActiveRange();
  var activeSht = SpreadsheetApp.getActiveSheet();
  var activeformula = activeRg.getFormula();
  var countRangeAddress = activeformula.match(/\((.*)\,/).pop().trim();
  var backGrounds = activeSht.getRange(countRangeAddress).getBackgrounds();
  var colorRefAddress = activeformula.match(/\,(.*)\)/).pop().trim();
  var BackGround = activeSht.getRange(colorRefAddress).getBackground();
  var countCells = 0;
  for (var i = 0; i < backGrounds.length; i++)
    for (var k = 0; k < backGrounds[i].length; k++)
      if ( backGrounds[i][k] == BackGround )
        countCells = countCells + 1;
  return countCells;
};

5. Sla vervolgens deze scriptcode op en ga terug naar het blad, voer deze formule in: = Countcoloredcells (A1: E11, A1) in een lege cel en druk vervolgens op invoeren toets om het berekende resultaat te krijgen. Zie screenshot:

Notes: In deze formule: A1: E11 is het gegevensbereik dat u wilt gebruiken, A1 is de cel gevuld met specifieke kleuren die u wilt tellen.

6. Herhaal de bovenstaande formule om andere specifieke gekleurde cellen te tellen.


Som-celwaarden op basis van celkleur met script in Google-spreadsheet

Als u de celwaarden wilt optellen met een specifieke celkleur, moet u onderstaande scriptcode toepassen.

1. Klik op Gereedschap > Script-editor om het projectvenster te openen en klik op filet > New > Script bestand om nog een nieuwe codemodule in te voegen, typt u in het aanwijzervak ​​een naam voor dit script, zie screenshot:

2. Klikken OK en in de geopende codemodule, kopieer en plak onder de scriptcode om de originele code te vervangen, zie screenshot:

function sumColoredCells(sumRange,colorRef) {
  var activeRg = SpreadsheetApp.getActiveRange();
  var activeSht = SpreadsheetApp.getActiveSheet();
  var activeformula = activeRg.getFormula();
  var countRangeAddress = activeformula.match(/\((.*)\,/).pop().trim();
  var backGrounds = activeSht.getRange(countRangeAddress).getBackgrounds();
  var sumValues = activeSht.getRange(countRangeAddress).getValues();  
  var colorRefAddress = activeformula.match(/\,(.*)\)/).pop().trim();
  var BackGround = activeSht.getRange(colorRefAddress).getBackground();
  var totalValue = 0;
  for (var i = 0; i < backGrounds.length; i++)
    for (var k = 0; k < backGrounds[i].length; k++)
      if ( backGrounds[i][k] == BackGround )
        if ((typeof sumValues[i][k]) == 'number')
          totalValue = totalValue + (sumValues[i][k]);
  return totalValue;
};

3. En sla deze code vervolgens op, ga terug naar het blad en voer deze formule in: = Sumcoloredcells (A1: E11, A1) in een lege cel en druk op invoeren sleutel om het berekende resultaat te krijgen, zie screenshot:

Notes: In deze formule: A1: E11 is het gegevensbereik dat u wilt gebruiken, A1 is de cel met een specifieke achtergrondkleur die u wilt optellen.

4. En dan kunt u de bovenstaande formule herhalen om andere specifieke gekleurde cellen op te tellen.


Tel of sum celwaarden op cel kleur met Kutools voor Excel in Microsoft Excel

In Microsoft Excel de celwaarden tellen of optellen op basis van de specifieke celkleur, Kutools for Excel's Count by Color hulpprogramma kan u helpen deze taak zo snel mogelijk af te maken.

Kutools for Excel : met meer dan 300 handige Excel-invoegtoepassingen, gratis om zonder beperking te proberen in 60-dagen.

Na het installeren van Kutools for Excel, doe alsjeblieft als volgt:

1. Selecteer de cellen die u wilt tellen of optellen op basis van de celkleur en klik vervolgens op Enterprise > Count by Colorzie screenshot:

2. In de Count by Color dialoogvenster, kies Standaard opmaak van de Kleur methode vervolgkeuzelijst en selecteer vervolgens Achtergrond van de Tel type drop-down, zie screenshot:

3. Dan klikken Rapport genereren knop en een nieuw werkblad met de berekende resultaten wordt in één keer gegenereerd, zie screenshot:

Notes: Met deze krachtige functie kunt u de celwaarden ook berekenen op basis van voorwaardelijke opmaak of lettertypekleur.

Klik nu op Download en gratis proef Kutools voor Excel!


Kutools voor Excel - De beste Office-productiviteitstool Verhoog uw productiviteit met 80%

  • Super Formula Bar (bewerk eenvoudig meerdere regels tekst en formule); Lay-out lezen (gemakkelijk grote aantallen cellen lezen en bewerken); Plakken op gefilterd bereik...
  • Cellen / rijen / kolommen samenvoegen en gegevens bewaren; Inhoud gesplitste cellen; Combineer dubbele rijen en som / gemiddelde... voorkomen dubbele cellen; Ranges vergelijken...
  • Selecteer Dupliceren of Uniek rijen; Selecteer Lege rijen (alle cellen zijn leeg); Super Find en Fuzzy Find in veel werkboeken; Willekeurig selecteren ...
  • Exacte kopie Meerdere cellen zonder formule-referentie te wijzigen; Automatisch referenties maken naar meerdere vellen; Voeg kogels toe, Selectievakjes en meer ...
  • Favoriete en snel formules invoegen, Bereiken, grafieken en afbeeldingen; Coderen van cellen met wachtwoord; Maak een mailinglijst en stuur e-mails ...
  • extract Text, Tekst toevoegen, verwijderen op positie, Verwijder de spatie; Subtotalen voor paging maken en afdrukken; Converteren tussen cellen Inhoud en opmerkingen...
  • Super filter (bewaar en pas filterschema's toe op andere bladen); Geavanceerde sortering per maand / week / dag, frequentie en meer; Speciaal filter door vet, cursief ...
  • Combineer werkmappen en werkbladen; Tabellen samenvoegen op basis van sleutelkolommen; Gegevens splitsen in meerdere bladen; Batch Converteer xls, xlsx en PDF...
  • Meer dan 300 krachtige functies. Werkt met Office 2007-2019 en 365. Ondersteunt alle talen. Eenvoudig te implementeren in bedrijf. Volledige functionaliteit 60-daagse gratis proefversie.
kte-tab 201905

Tabblad Office Brengt interface met tabbladen naar Office en maakt uw werk veel eenvoudiger

  • Bewerken en lezen met tabbladen inschakelen in Word, Excel, PowerPoint, Publisher, Access, Visio en Project.
  • Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster, in plaats van in nieuwe vensters.
  • Verhoogt uw productiviteit met 50% en verlaagt dagelijks honderden muisklikken voor u!
Officetab onderaan
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Nataly · 29 days ago
    Hola! Muchas gracias por tu artículo, ha sido de mucha ayuda, he logrado corregir el tema del formato regional de google cambiando . por ;
    Lo que no consigo hacer es que se actualice la formula cuando ingreso nuevos datos. ¿Sabes cómo hacer esto?
    Gracias
  • To post as a guest, your comment is unpublished.
    Kevin · 2 months ago
    Hi everyone. I use this sumColoredCells function but it will NOT auto refresh if you change cell colors. The only way it refreshes is to change a value in the range. A work-around I found was to create a checkbox somewhere to add 1 (or use insignificant fractional (.000001) if you are rounding) to a number in the range, and unchecking will add 0. This will act as a refresh "toggle" switch. Not really ideal, I know... but this is for my personal use and only a minor hassle.
  • To post as a guest, your comment is unpublished.
    Francisco · 2 months ago
    Thank you so much, truly useful!
  • To post as a guest, your comment is unpublished.
    Roberto · 2 months ago
    There were some errors on the code, like the range not being used at all and that the calculation was only made when the parameters were changed. With this version the calculation will be updated when there is an update on the sheet and the range has to be passed as a string, also some dummy cell has to be used to save a random value, you can put the color of the text just like the background to make it invisible:

    =countColoredCells("B3:B145","B1",$A$1)

    function countColoredCells(countRange, colorRef, unUsed) {
    var backGrounds = SpreadsheetApp.getActiveSheet().getRange(countRange).getBackgrounds();
    var backGround = SpreadsheetApp.getActiveSheet().getRange(colorRef).getBackground();
    var countCells = 0;
    for (var i = 0; i < backGrounds.length; ++i)
    for (var k = 0; k < backGrounds[i].length; ++k)
    if ( backGrounds[i][k] == backGround )
    ++countCells;
    return countCells;
    }

    function onEdit(e)
    {
    SpreadsheetApp.getActiveSheet().getRange('A1').setValue(Math.random());
    }
  • To post as a guest, your comment is unpublished.
    Marco · 6 months ago
    A quick solution but not the best is to add a new parameter and never use it, something like this:

    function sumColoredCells(sumRange,colorRef,test)


    The test parameter is never used, this issue here is that the values only will be refresed if first of all you set the color and then change the value.
  • To post as a guest, your comment is unpublished.
    Adam · 6 months ago
    This script used to work great, but it doesn't work anymore. Is there a change that brought it down?
  • To post as a guest, your comment is unpublished.
    Steve · 7 months ago
    Internet is full of instances of this solution and it doesn't work.
  • To post as a guest, your comment is unpublished.
    Marco · 7 months ago
    Hi there,
    on Google sheets, seems not to be working, I went on debug and for both processes, in row 5 seems to be a type error, the message is: impossible to recall the "pop2 method of null. Does it makes sense? Thanks a lot!
  • To post as a guest, your comment is unpublished.
    desi · 7 months ago
    thank you :*
  • To post as a guest, your comment is unpublished.
    Pedro Johnston · 7 months ago
    Hello,


    This function works, however, when i try to sum 2 instances of it in the same cell as in (in my case):


    =sumColoredCells(A103:AW103, C171) + sumColoredCells(A138:AW138, C171)


    It gives me an #Error - Range not found (line 6).


    It doesn't work even if i use SUM() or ADD(). Any ideas?


    Thanks in advance.
  • To post as a guest, your comment is unpublished.
    Geoff · 9 months ago
    The SumColoredCells Script works great. Do you know if there is a way to display (in a single cell) the sum of 2 colors? For example, if I wanted the sum of both the Yellow and Green Cells to display in a single cell, is that possible? I would think the formula would be:
    =SUM(sumcoloredcells(A1:E11,A1)),(sumcoloredcells(A1:E11,A5)) ....But that results in an ERROR. Any ideas?
    • To post as a guest, your comment is unpublished.
      skyyang · 9 months ago
      Hello, Geoff,
      May be there is no direct formula to solve your problem, but, you can sum the colored cells separately first, and then, sum the two calculated results as you need.
      • To post as a guest, your comment is unpublished.
        Geoff · 9 months ago
        Thanks for the reply. I have done that, but would love to consolidate the formula into a single cell, as I have to update the ranges every week. Anyway, thanks for checking!
  • To post as a guest, your comment is unpublished.
    jchew · 9 months ago
    Thanks, this worked great!
  • To post as a guest, your comment is unpublished.
    Nikolaj Justin Kinas · 11 months ago
    Hello,
    The formula does not work for me.
    If I do this in spreadsheet only errors will occur.
  • To post as a guest, your comment is unpublished.
    Danison Rarama · 11 months ago
    im trying to do something like this
    =countColoredCells (H62:H,B2)countif("MBA13R15")
  • To post as a guest, your comment is unpublished.
    Danison Rarama · 11 months ago
    Hi there,


    Can I need some help with a similar formula to SUM total of number keywords matching and reflecting the number of colors.


    What I what to achieve it determine the number of laptops which is a keyword but recognise the Available based on color of the cells?
  • To post as a guest, your comment is unpublished.
    Raul · 1 years ago
    Replace this lines:
    var countRangeAddress = activeformula.match(/\((.*)\;/).pop().trim();


    var colorRefAddress = activeformula.match(/\;(.*)\)/).pop().trim();

    Notice than , was changed for ;
  • To post as a guest, your comment is unpublished.
    Sam · 1 years ago
    does it auto update when new data is added?
  • To post as a guest, your comment is unpublished.
    Terry · 1 years ago
    I keep getting an error "Action not allowed (Line 0)". What does this mean?
  • To post as a guest, your comment is unpublished.
    Terry · 1 years ago
    I'm getting "Action Not Allowed Line 0" What am I doing wrong?
  • To post as a guest, your comment is unpublished.
    KydKat · 1 years ago
    Is there a way to include negative numbers in the mix and get the correct sum? Mine keeps adding all the numbers in the range, even if the number has a negative sign on it.
  • To post as a guest, your comment is unpublished.
    Laura · 1 years ago
    Hey, thanks for this script. I'm having a problem in the script for google sheets: TypeError: Cannot call method "pop" of null. (line 5, file "SumByColor"). Can you please help?, thanks
    • To post as a guest, your comment is unpublished.
      Patodox · 11 months ago
      The problem maybe is your regional configuration, this script thinks you are separating parameters by ',', but it is not your case probably. In my case, I use ';', so I modified the script, changing the character in the lines

      var countRangeAddress = activeformula.match(/\((.*)\;/).pop().trim();

      var colorRefAddress = activeformula.match(/\;(.*)\)/).pop().trim();
    • To post as a guest, your comment is unpublished.
      Robinson Tapia · 1 years ago
      Hello, I have the same problem, have you fix it?
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hi, Laura,
      The script works well in my google sheets.
      If there is problem in your google sheet, I can share my google sheet with you.
      Please give your email address.
      Thank you!
  • To post as a guest, your comment is unpublished.
    Dave Gorman · 1 years ago
    Your sumColoredCells() function is incorrect as it doesn't use sumRange
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hello, Dave,
      The above script works well in my Google sheet.
      When applying the formula, you should save the script window first.
      Please try it, Thank you!
  • To post as a guest, your comment is unpublished.
    Todd · 1 years ago
    Thanks for the help with "Count cells based on color for google sheets". Limitation seems to be that when cell colors are changed the sheet does not refresh and totals are incorrect. I have to delete the code in the total cell and copy again from neighbour cell. Not ideal, but it works. Thanks again.
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hello, Todd,
      Yes, as you said, you should retype the formula when the cell color changes.
      May be there is no other good ways to solve this problem.
      If any other has good methods, please comment here.
      Thank you!
  • To post as a guest, your comment is unpublished.
    Jose · 1 years ago
    Hey, thanks for this script. I'm having a problem in the script for google sheets: TypeError: Cannot call method "pop" of null. (line 5, file "SumByColor"). Can you please help?, thanks
  • To post as a guest, your comment is unpublished.
    Sheldon · 1 years ago
    Hi! Great script but I run into a problem when trying to edit the values within the coloured cells. The script does not refresh the values if I change the colour of the cell. I can only force it to refresh if i delete the contents of the cell, change the colour and then enter the value again.