Without using VBA, is there a way to count how many GREEN cells there are in a row and display the sum of said green cells in Cell names Total
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Without using VBA, is there a way to count how many GREEN cells there are in a row and display the sum of said green cells in Cell names Total
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Without using VBA, is there a way to count how many GREEN cells there are in a row and display the sum of said green cells in Cell names Total

    We'll start there in the subject.
    I have been racking my brains.

    In a row, you have a persons name.
    I want next to that name how many GREEN cells are on that row that he is on.
    He is on A4.
    The data range for his name is from L4 to NL4.
    Come cells have an orange, others yellow. Just want the green one summed up for now.

    So for instance is he has 8 green cells in that range.
    I would like it to display 8 in D4.

    Confusing enough?

    No VBA unfortunately.

    My best to you all...

    Kevalson
    Last edited by Kevalson; Aug 8th, 2019 at 11:06 AM.

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,803
    Post Thanks / Like
    Mentioned
    458 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Without using VBA, is there a way to count how many GREEN cells there are in a row and display the sum of said green cells in Cell names Total

    How are the cells getting their colour?
    Manually, or via conditional formatting?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,392
    Post Thanks / Like
    Mentioned
    54 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Without using VBA, is there a way to count how many GREEN cells there are in a row and display the sum of said green cells in Cell names Total

    Take a look here: https://trumpexcel.com/count-colored-cells-in-excel/

    Also, if the cells are colored by Conditional Formatting, you should be able to use the same conditions used in Conditional Formatting in COUNTIF, COUNTIFS, or SUMPRODUCT formulas to get the counts.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  4. #4
    New Member
    Join Date
    Apr 2002
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Without using VBA, is there a way to count how many GREEN cells there are in a row and display the sum of said green cells in Cell names Total

    Manually
    And it needs to count on the row., not column.
    So how many green cells are from L4 through NL4 for instance.
    Last edited by Kevalson; Aug 8th, 2019 at 11:32 AM.

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,803
    Post Thanks / Like
    Mentioned
    458 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Without using VBA, is there a way to count how many GREEN cells there are in a row and display the sum of said green cells in Cell names Total

    You could use the Get.Cell approach
    In the Name manager select "New" give it a name (I've used IsColoured) and enter this formula
    =GET.CELL(63,INDIRECT("rc[-365]",FALSE))
    Then in column NM put =IsColoured and fill right to column ABM
    You than then use a countif to count those cells.

    However changing a colour will not force a recalculation so you will have to do that yourself. It is also likely make your workbook a lot larger & possibly slower.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  6. #6
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,239
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Without using VBA, is there a way to count how many GREEN cells there are in a row and display the sum of said green cells in Cell names Total

    Quote Originally Posted by Kevalson View Post
    Manually
    What is the criterion (logic) that is used to manually color cells green? Greater than a certain value? Does it contain some keyword? Or any other criteria (?)
    I believe they are not randomly colored, or are they?

    M.

  7. #7
    New Member
    Join Date
    Apr 2002
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Without using VBA, is there a way to count how many GREEN cells there are in a row and display the sum of said green cells in Cell names Total

    no not randomly.

    It's a calendar that is someone takes a day off with pay, we manually turn that cell green on that particular date.

  8. #8
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,239
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Without using VBA, is there a way to count how many GREEN cells there are in a row and display the sum of said green cells in Cell names Total

    Quote Originally Posted by Kevalson View Post
    no not randomly.

    It's a calendar that is someone takes a day off with pay, we manually turn that cell green on that particular date.
    Then you should use the solution suggested above by Fluff or VBA. Unfortunately, as far as I know, a formula can't identify a colored cell.

    M.

  9. #9
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,803
    Post Thanks / Like
    Mentioned
    458 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Without using VBA, is there a way to count how many GREEN cells there are in a row and display the sum of said green cells in Cell names Total

    Are the cells empty?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  10. #10
    New Member
    Join Date
    Apr 2002
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Without using VBA, is there a way to count how many GREEN cells there are in a row and display the sum of said green cells in Cell names Total

    No. They have some data in them.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •