View original (French)

SUMIF color criteria

Asfero Posted messages 22 Status Member -  
Asfero Posted messages 22 Status Member -
Hello,

I would like to use the SUMIF function with the criterion being the color of the numbers.
How should I write it?
=SUMIF(.......)

I want to sum the red numbers from column A1 to A10 for example (only if they are red)

And how do I use a different color that I have defined myself based on RGB???

Thank you
Configuration: Windows XP Firefox 3.0.10

13 answers

  1.
    michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
     
    Hello,

    Is the red color of your numbers manual or from conditional formatting (following your first post earlier)?

    --
    Best regards, Michel
    2
    1. Asfero Posted messages 22 Status Member
       
      MICHEL if you could clarify things for me, I would appreciate it.

      thank you
      0
  2. Asfero Posted messages 22 Status Member
     
    Hello,

    It comes from a macro associated with a checkbox. When I check the box, my number turns red. However, I would like to use a different color that I have defined in my color palette.

    That's it, and I would like a cell to sum all my numbers for which I have checked the box.

    Thank you
    0
    1. michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
       
      Here are the different color codes used in VBA
      https://www.cjoint.com/?fnpWeKdUAL

      Could you show your macro, please?
      --
      Best regards, Michel
      0
      1. Asfero Posted messages 22 Status Member > michel_m Posted messages 18903 Registration date   Status Contributor Last intervention  
         
        Michel

        Here is the code for my checkbox

        Private Sub CheckBox1_Click()
        If Checkbox1.Value = True Then
        [F56] = [D56]
        [J56].Font.ColorIndex = 3

        Else
        [F56] = ""
        [J56].Font.ColorIndex = 0
        End If
        End Sub

        I would like to sum the cells in J for which I have only checked the box, meaning those that are colored?

        So my question is, since I don't know anything about VBA, how do I write the sum formula with a color in number VBA? And then how do I associate this code with the cell where I want the sum to take place?

        Thank you for being precise as I really don’t know anything….
        0
  3. qmike Posted messages 1599 Registration date   Status Member Last intervention   594
     
    Hello
    in Excel
    There are no standard formulas that allow you to sum colored cells
    However,
    You can use VBA

    Take a look here for an example

    http://bvrve.club.fr/Astuces_Michel/05excelvba.html#Color1

    Have a nice day
    0
  4. Asfero Posted messages 22 Status Member
     
    Thank you, Mike, but what do I do if I don't want to use red?

    How do I write the code?

    Michel

    Here is the code for my checkbox

    Private Sub CheckBox1_Click()
    If Checkbox1.Value = True Then
    [F56] = [D56]
    [J56].Font.ColorIndex = 3

    Else
    [F56] = ""
    [J56].Font.ColorIndex = 0
    End If
    End Sub

    I would like to sum the J cells for which I've only checked the box, so which is the color?

    My question is, since I don't know anything about VBA, how do I write the sum formula with a VBA color number? And then how do I associate this code with the cell where I want the sum to happen?

    Thank you for being precise because I really don't know anything ....
    0
    1. michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
       
      Alright!

      There are a few small things to improve in your checkbox macro, but it's not a big deal!

      The best thing, if you're limited in VBA, would be for you to join the sheet of your workbook with the checkboxes on
      https://www.cjoint.com/
      and paste the provided link in your message
      --
      Best regards, Michel
      0
    2. Asfero Posted messages 22 Status Member > michel_m Posted messages 18903 Registration date   Status Contributor Last intervention  
       
      The problem is that it's not finished. I wanted to understand the principle in order to do the entire table afterwards because I have about a hundred checkboxes to make.

      Is there really no code you can write for me with an example of color and box that I could adjust later?

      If I have the code written, I could manage, but I need an example with several cells and a color in numbers for VBA.

      Let's say I want to sum the cells A56 to J56 among which the numbers are brown color 53.

      Thank you for your help.
      0
    3. michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320 > Asfero Posted messages 22 Status Member
       
      Re,

      Here is a function that automatically calculates the sum of the "marron53" 

      Function additionner_marron(plage As Range) Dim cellule As Range, total As Double For Each cellule In plage If cellule.Font.ColorIndex = 53 Then total = total + cellule End If Next additionner_marron = total End Function


      Installation:
      You need to copy-paste it into a VBE module. You can find out how to do it in the "tips" section of CCM written by our friend gbinforme (it's indeed your article gbinforme, thank you)
      http://www.commentcamarche.net/faq/sujet 8130 updating a procedure or function in vba

      Then
      Insert - Functions - Custom Functions, click on "additionner_marron" and select the range
      (A56:J56) in your message


      Checkboxes:
      I need to search in my attic for a technique for the 100 Checkboxes; so probably tomorrow...
      --
      Best regards, Michel
      0
    4. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281 > michel_m Posted messages 18903 Registration date   Status Contributor Last intervention  
       
      Good evening,

      I had prepared something but Michel was faster than me...
      I'll share it anyway and besides, it spares me from the detailed explanations he just provided you.

      The difference with Michel's macro is that, to allow for more flexibility given your indecision about colors, you can define it in a cell. And Michel made you a custom function, here it's a macro to run.

      Choose a cell on your sheet, K1 for example, and name your cell Total1 (make sure to respect the capitalization).
      Paste this code as Michel explained above: 
      Sub total1() Dim c As Range, tot1 As Double For Each c In [J56:J58] If c.Interior.ColorIndex = Range("Total1").Interior.ColorIndex Then tot1 = tot1 + c.Value End If Next c Range("Total1").Value = tot1 End Sub


      Set your background color in K1, run the macro, and the total of the cells (in J56:J58) having the same background color as K1 will be placed in K1.
      Example

      Eric
      0
    5. michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320 > eriiic Posted messages 24581 Registration date   Status Contributor Last intervention  
       
      Re, Eric

      Ultimately, I gave up the function to go back to 1 parameterized subroutine as the function was triggering every time we checked or unchecked, resulting in aberrant outcomes!

      So in module1 we have: 
      Sub additionner_53(plage As String) Dim cellule As Range, total As Double For Each cellule In Range(plage) If cellule.Font.ColorIndex = 53 Then total = total + cellule End If Next Range("K56") = total End Sub


      And in modulefeuil1: 
      Private Sub CheckBox1_Click() If CheckBox1 Then [F56] = [D56] [J56].Font.ColorIndex = 53 Else [F56] = "" [J56].Font.ColorIndex = vbnothing End If additionner_53 ("A56:J56") End Sub attached is a demo

      https://www.cjoint.com/?fnswKQcqWS

      The big job remains the configuration of the 100 Checkboxes but I don’t want to drag you into class modules, Asfero... Unless Eric or someone else knows a trick to do these configurations easily...



      --
      Best regards, Michel
      0
  6. qmike Posted messages 1599 Registration date   Status Member Last intervention   594
     
    no worries, you put the color code corresponding to the desired color
    0
  7. qmike Posted messages 1599 Registration date   Status Member Last intervention   594
     
    To get all the color codes
    Run the following macro

    Sub Show_Colors()
    For i = 1 To 56
    Cells(i, 1).Interior.ColorIndex = i
    Next i
    End Sub
    0
  8. Asfero Posted messages 22 Status Member
     
    Hi Mike

    here is the code you proposed

    Sub TotalisationCouleurRouge()

    Set Aselectionner = Application.InputBox _
    (prompt:="select the range of cells ", _
    Title:=" Range of cells to select", Type:=8)
    Aselectionner.Select 'Selecting the desired range
    For Each cel In Aselectionner
    If cel.Interior.ColorIndex = 3 Then SomRouge = SomRouge + cel
    Next cel
    MsgBox SomRouge
    End Sub

    what should I do when it says CouleurRouge
    SomRouge ???

    and how do I write the range of my cells when it says
    "select the range of cells "
    " Range of cells to select"

    thank you
    0
  9. qmike Posted messages 1599 Registration date   Status Member Last intervention   594
     
    During the execution of the macro
    You select the cells to be totaled
    in this example, you will have a total of the red cells.
    0
  10. Asfero Posted messages 22 Status Member
     
    I'm sorry, but I can only provide translations. Please provide the text you would like translated.
    0
  11. qmike Posted messages 1599 Registration date   Status Member Last intervention   594
     
    Listen, apparently you're not following the procedures mentioned in the previous posts.
    Don't try to complicate things.
    So we're stopping it here!!!

    Have a good evening.
    0
  12. Asfero Posted messages 22 Status Member
     
    Hi,

    what I need is a clear explanation because I know nothing about it, not references to general explanations.

    I need to understand with my data, not general data, because I don't know how to write the codes...

    that's all
    0
  14. Asfero Posted messages 22 Status Member
     
    WOAAAAA ok

    Thank you both!!!! Unfortunately, I've been short on time these last few days to try all of this.

    I appreciate your help and you'll see me again if I haven't managed it hehe.

    Thank you so much!!!!!!!!!!
    0