Results 1 to 2 of 2

Thread: Quick question: Conditional counting and then some...

  1. #1

    Quick question: Conditional counting and then some...



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hey guys!

    This is what i have at the moment, (its a mailing list)

    CELL B5 =COUNTIF(*****!U2:U101,"YES")
    CELL C5 =COUNTIF(*****!U2:U101,"NO")
    CELL D5 =COUNTIF(*****!U2:U101,"RETURNED")

    What I want to do is every time we color the cell a certain color like yellow, it will not count that cell.

    Every month we submit a separate sheet to accounting and this should keep it updated without the hassle of manually counting. But every month we only count only the un-highlighted ones.

    So what can I do to have it not count yellow highlighted cells???

    Thanks!

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    452
    Articles
    0
    Threads - GoSkills - Groupon

    Register for a FREE account, and/
    or Log in to avoid these ads!

    Counting colours involves VBA.

    You can instead use an in-cell trigger, example an "x" or checkmark, etc in a cell within the same row... then you can use conditional formatting to highlight the row based on that trigger being present... then you can use COUNTIFS (if you are in XL2007 or later) or SUMPRODUCT (for any version).

    e.g.

    =COUNTIFS(Sheet1!U2:U101,"YES",Sheet1!V2:V101,"x")

    or

    =SUPRODUCT((Sheet1!U2:U101="YES")*(Sheet1!V2:V101="x"))

    where column V contains your trigger.
    Microsoft MVP - Excel

Posting Permissions

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