Page 1 of 3 123 LastLast
Results 1 to 10 of 23

Thread: how to count and sum cells by color in excel
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Feb 2018
    Location
    Midlands, UK
    Posts
    912
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default how to count and sum cells by color in excel

    Hi I have the formula below where I am trying to count how many cells have a colour and counting, where my range is to a and the cell colour is yellow which is p1, but the code doesn't work I get a #NAME ? please can u help?

    =ASAPCOUNTBYCELLCOLOR(A:A,P1)

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,062
    Post Thanks / Like
    Mentioned
    469 Post(s)
    Tagged
    47 Thread(s)

    Default Re: how to count and sum cells by color in excel

    Do you have the add-in loaded?
    - 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
    Board Regular Sam_D_Ben's Avatar
    Join Date
    Oct 2012
    Location
    New Jersey, USA
    Posts
    371
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: how to count and sum cells by color in excel

    I guess, ASAP is an Add-in utility, do you have this in Excel?
    Last edited by Sam_D_Ben; Sep 9th, 2019 at 09:50 AM.
    Sam_D_Ben

  4. #4
    Board Regular Sam_D_Ben's Avatar
    Join Date
    Oct 2012
    Location
    New Jersey, USA
    Posts
    371
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: how to count and sum cells by color in excel

    Quote Originally Posted by Fluff View Post
    Do you have the add-in loaded?

    Hey Fluff, I dint see your comment. I am sorry.
    Sam_D_Ben

  5. #5
    Board Regular
    Join Date
    Feb 2018
    Location
    Midlands, UK
    Posts
    912
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: how to count and sum cells by color in excel

    hi sorry no not got any add in

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,062
    Post Thanks / Like
    Mentioned
    469 Post(s)
    Tagged
    47 Thread(s)

    Default Re: how to count and sum cells by color in excel

    In that case that's why you get #NAME? in the cell.
    You are trying to call a UDF (user defined function) which you don't have.
    Last edited by Fluff; Sep 9th, 2019 at 09:57 AM.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #7
    Board Regular
    Join Date
    Feb 2018
    Location
    Midlands, UK
    Posts
    912
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: how to count and sum cells by color in excel

    oww can you help me with that

  8. #8
    Board Regular Sam_D_Ben's Avatar
    Join Date
    Oct 2012
    Location
    New Jersey, USA
    Posts
    371
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: how to count and sum cells by color in excel

    Also i guess it can be downloded.
    Sam_D_Ben

  9. #9
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,062
    Post Thanks / Like
    Mentioned
    469 Post(s)
    Tagged
    47 Thread(s)

    Default Re: how to count and sum cells by color in excel

    Just go to their site & download it.
    - 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
    Board Regular
    Join Date
    Feb 2002
    Location
    North Yorkshire, UK
    Posts
    263
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: how to count and sum cells by color in excel

    This is a question that gets asked a lot, and while it is possible to do (using an add-in like ASAP Utilities, or custom VBA code), it's generally not a good idea to do so.

    Presumably, each colour is used to indicate that a values that match one or more criteria based on text, numeric, or boolean values in other columns. It's much easier to use SUMIF or SUMIFS and specify the criteria (the same ones that have been used to "flag" the colour used) than it is to use a custom function to sum by colour which is not a native Excel function.

    For example, if you want to sum of column B for all of the red cells, but cells highlighted in red are those where department (in Column A) is "Alpha", and sales (in column B) are greater than 5000, then you can use:
    =SUMIFS(B:B,A:A,"Alpha",B:B,">="&5000)
    It's curtains for your Dr. Horrible ... lacy, gently wafting curtains.

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
  •