+ Reply to Thread
Results 1 to 2 of 2

Count number of occurrences and return value

  1. #1
    Registered User
    Join Date
    03-28-2019
    Location
    Copenhagen, Denmark
    MS-Off Ver
    Office 365
    Posts
    1

    Count number of occurrences and return value

    Hi All,


    I have a Lead sheet, where I type in Hot and cold leads, tagging them red or green.
    In the bottom of the sheet I want to do a sum of the values from a range of the red cells, for instance A1:A120


    1. Find out what cells are red
    2. Do a SUM of the values of all red cells in a specific column range

    I have the VBA formula for checking the color in the cell, but I cannot figure out how to SUM the value of a range of "found" cells.


    I thought about vlookup or Countif, but so far did not solve this.

    Anybody with the magic beans, that can help?



    Kind regards...Jan

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,038

    Re: Count number of occurrences and return value

    Formulas cant detect the color of a cell (unless its conditional formatting, in which case use the same conditions) so youre only hope is VBA.

    Set a variable to 0.
    Loop through the column.
    If the cell colour is what you want increment the variable
    At the end of the loop the variable will contain the number of cell colours you checked for.

    or use the VBA to set 1 flag 1 or 0 in a Helper column and just use SUM() for that column.
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Count number of occurrences and return in text
    By Meggo12 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-04-2016, 09:14 AM
  2. Count number of occurrences
    By kris18 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-29-2015, 05:33 AM
  3. [SOLVED] Count number of occurrences but if there are consecutive matches count them as one
    By michaljireht in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-26-2013, 08:26 PM
  4. Count number of occurrences in a day
    By quinnbri in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-20-2013, 05:49 PM
  5. [SOLVED] Count number of occurrences for a number of ranges from a find loop
    By ANDREWA in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 08-23-2012, 07:53 PM
  6. Count number of occurrences
    By MarkM in forum Excel General
    Replies: 1
    Last Post: 07-27-2006, 05:15 PM
  7. Count Number of Occurrences in a Column
    By anniejhsu in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-31-2006, 04:35 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1