+ Reply to Thread
Results 1 to 8 of 8

Count conditional formatting formula

  1. #1
    Registered User
    Join Date
    11-28-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    4

    Count conditional formatting formula

    Ok, I've searched quite a bit on this topic and i still can't find what i'm looking for.

    I have two columns, both with dates, one for completion and one for delivery.

    I have set up conditional formatting to highlight the delivery column in green if the date is less than or equal to the completion date + 14 days and red if the delivery date is greater than 14 days after the completion date.

    Completion Delivered ConditionalFormattingColour
    12/05/2013 14/06/2013 Red
    30/01/2013 08/02/2013 Green
    09/05/2013 14/06/2013 Red
    31/01/2013 08/02/2013 Green
    31/01/2013 08/02/2013 Green
    01/02/2013 08/02/2013 Green
    12/02/2013 06/03/2013 Red

    What i'm trying to do now is have a single cell which shows how many delivery cells are green (i.e. delivery cells that are <= completion +14). For the above example i'm looking for a cell with 4 in.

    I've tried countif and countifs and sumproduct but i can't for the life of me figure out how to get the cell range to add 14 days to each cell and count the number of cells that have a delivery date less than or equal to 14 days later the completion date. I'm not that concerned with counting the number of cells that meet the conditional formatting colour criteria as i've seen that i'd need to write some vba to do this and i'm not wanting to go down that route just yet. I'm sure there is a formula that can be written for this, but i'm not clever enough to figure it out just yet.

    Hopefully i've attached a sample of the cells with the current conditional formatting and any help with this would be greatly appreciated.

    CountCFDates.xlsx

  2. #2
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Count conditional formatting formula

    I didn't look at your file.

    Try this:

    =SUMPRODUCT(--(B2:B8<=A2:A8+14))
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Count conditional formatting formula

    An easy way could be this.

    In C2 and copy down use this.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    You can hide this column.

    Then use

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  4. #4
    Registered User
    Join Date
    11-28-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Count conditional formatting formula

    Tony - Thanks, but it doesn't give the right results (I've also tried variations on it to no avail)

    Fotis - A seperate column/s is something that i would rather avoid if i can do it all in one cell formula. Just heading home now, but will have another crack at it tomorrow.

    Thanks

  5. #5
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Count conditional formatting formula

    =SUMPRODUCT(--(B2:B8<=A2:A8+14)) gives 4 for your example
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  6. #6
    Registered User
    Join Date
    11-28-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Count conditional formatting formula

    Ok, so Martin and Tony, i tried this at home and it does appear to work here so i'm not sure what i was doing earlier. I'll try it again in the office tomorrow, but it does look a little like it's doing what i want. A hopeful thank you.

  7. #7
    Registered User
    Join Date
    11-28-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Count conditional formatting formula

    Ok, i'm now back in the office and i've tried out a few variations and i've got it to work.

    My actual spreadsheet had some slightly different entries to the sample i posted and that was where i was getting errors - i had some text next to the date in one cell and some blank cells in the delivered column which were giving me errors. I took out the text (which wasn't too important and i can have it as a comment) and altered the formula to not count blank cells.

    My final formula (specific to my actual spreadsheet cells rather than the demo cells) is:

    =SUMPRODUCT(IF(V8:V36="",0,(--(V8:V36<=T8:T36+14))))

    and it's entered as an array formula (CTRL+SHIFT+ENTER)

    Thanks for all the help, query solved!

  8. #8
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Count conditional formatting formula

    Here's another way to write that formula:

    =SUMPRODUCT(--(V8:V36<>""),--(V8:V36<=T8:T36+14))

+ 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. Apply Conditional Formatting Formula to Count output
    By mboukili in forum Excel General
    Replies: 7
    Last Post: 06-10-2013, 10:57 AM
  2. count if formula based of conditional formatting??
    By Lorna B in forum Excel General
    Replies: 7
    Last Post: 06-16-2012, 12:31 PM
  3. Count times when conditional formatting met
    By suzy_f in forum Excel General
    Replies: 4
    Last Post: 08-19-2011, 07:38 AM
  4. Conditional Formatting count
    By tennis55 in forum Excel General
    Replies: 1
    Last Post: 05-15-2011, 02:32 AM
  5. [SOLVED] Count conditional formatting
    By gocats in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-24-2005, 10:05 PM

Tags for this Thread

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