+ Reply to Thread
Results 1 to 13 of 13

counting cells filled with an unconditional cell filled with a color.

  1. #1
    Registered User
    Join Date
    07-10-2012
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    5

    counting cells filled with an unconditional cell filled with a color.

    HI All,

    I want to add up the number of time the word "IN" appears in a row. THere will be other words on the cells in the same row which should not return a value.

    WORDS

    IN = 1
    H = 0
    SICK = 0
    OFF = 0

    I am creating a staff rota and trying to make it simple to complete by copying entering the above words in the cells and end of the row will calculate the actual hours required.

    HELP
    Last edited by nils33; 07-10-2012 at 04:12 PM. Reason: used an inappropriate title

  2. #2
    Forum Contributor BenMiller's Avatar
    Join Date
    12-06-2011
    Location
    New Jersey
    MS-Off Ver
    Excel 2010
    Posts
    254

    Re: HELP with a formula

    =COUNTIF(1:1,"in")

  3. #3
    Registered User
    Join Date
    07-10-2012
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: HELP with a formula

    now i feel stupid - thanks

  4. #4
    Forum Contributor BenMiller's Avatar
    Join Date
    12-06-2011
    Location
    New Jersey
    MS-Off Ver
    Excel 2010
    Posts
    254

    Re: HELP with a formula

    No problem Glad I could help ... Appreciate the feedback!
    Last edited by Cutter; 07-10-2012 at 03:52 PM. Reason: Removed whole post quote

  5. #5
    Registered User
    Join Date
    07-10-2012
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: HELP with a formula

    does anyone know if you are able to count cells filled with color, based on the original tread question?

  6. #6
    Forum Contributor BenMiller's Avatar
    Join Date
    12-06-2011
    Location
    New Jersey
    MS-Off Ver
    Excel 2010
    Posts
    254

    Re: HELP with a formula

    Not with a formula; you'd need VBA. Are they filled as a result of "regular" or conditional formatting?
    Last edited by Cutter; 07-10-2012 at 03:52 PM. Reason: Removed whole post quote

  7. #7
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: HELP with a formula

    Hello nils33, and welcome to the forum. Thanks for changing your thread title.
    Last edited by Cutter; 07-10-2012 at 04:19 PM. Reason: Request for title change removed

  8. #8
    Registered User
    Join Date
    07-10-2012
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: counting cells filled with an unconditional cell filled with a color.

    sorry - i think i have now changed to an appropriate title.

  9. #9
    Forum Contributor BenMiller's Avatar
    Join Date
    12-06-2011
    Location
    New Jersey
    MS-Off Ver
    Excel 2010
    Posts
    254

    Re: counting cells filled with an unconditional cell filled with a color.

    Last edited by Cutter; Today at 03:52 PM. Reason: Removed whole post quote
    ...Seriously?

  10. #10
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: counting cells filled with an unconditional cell filled with a color.

    Seriously. Rule #12, as quoted below:

    12. Don't quote whole posts -- it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding.

  11. #11
    Forum Contributor BenMiller's Avatar
    Join Date
    12-06-2011
    Location
    New Jersey
    MS-Off Ver
    Excel 2010
    Posts
    254

    Re: counting cells filled with an unconditional cell filled with a color.

    The OP modified the original question to something completely different. If the color comes from a CF, the question might be extremely similar. Otherwise, it could be a whole different ballgame. I wanted him to know that I was responding to the latest post, especially in light of the fact that he added that post at the same time (3:26) I had posted something relating to the original post.
    If it would have been a long post, I would have merely said "regarding your second question...", or something of the sort. But quoting a two-line, nineteen-word post? I would hardly call that "clutter".
    I'm sorry for taking this thread off-topic.

    nils33, can you tell me where the formatting comes from?

  12. #12
    Registered User
    Join Date
    07-10-2012
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: counting cells filled with an unconditional cell filled with a color.

    HI Ben

    I was hoping to use the cell color "button" to then drag across a row to color the cells - i would then like the totaling cell to count the colored cells. Secondly i would like to know if the formula would recognize different colors and then count them accordingly.

  13. #13
    Forum Contributor BenMiller's Avatar
    Join Date
    12-06-2011
    Location
    New Jersey
    MS-Off Ver
    Excel 2010
    Posts
    254

    Re: counting cells filled with an unconditional cell filled with a color.

    Excel does not provide native formulas to deal with colors. Take a look here to see how to deal with them (at least it's a start!) ... or, the easier option would be to color them with conditional formatting, then do all your calculations based on that same logic.
    Unless you plan on coloring them randomly? I assume there's some sort of method to your madness
    Last edited by BenMiller; 07-10-2012 at 05:04 PM. Reason: forgot to post link

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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