+ Reply to Thread
Results 1 to 6 of 6

Conditional Formatting and Sum if... I think!

  1. #1
    Registered User
    Join Date
    09-24-2019
    Location
    boston
    MS-Off Ver
    16.29
    Posts
    6

    Conditional Formatting and Sum if... I think!

    I am trying to accomplish two things here.

    1) Conditional Formatting
    - So if a date is = to another date or is + 7 days after, than it will be green, if not then red. (trying to include a picture, but it's not uploading, I will do my best to explain!)
    if i have two dates:
    date 1 (cell E119) : 9/9/19
    Date 2(cell F119) : 9/10/19

    I want that second date to be green if its equal to "date 1" or within a 7 day range after that

    Hope that makes sense

    2) sumif
    To get to my next part, there are many work arounds that I know will work to get to the answer, but I feel like SumIf is the best option here.
    So, if that cell is green, I want to add the revenue associated with that date. + the revenue I have on another tab.

    I would connect these two data sets through a unique ID. Not sure if that is helpful here.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Conditional Formatting and Sum if... I think!

    It would help if you attached a sample Excel workbook.

    To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

    Don't try to use the Paperclip icon, as it doesn't work on this forum.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    09-24-2019
    Location
    ILkeston
    MS-Off Ver
    365
    Posts
    1

    Re: Conditional Formatting and Sum if... I think!

    2nd part why not have another column that is set (say to 1) if the condition is true. Then sumif that column is set to 1

  4. #4
    Registered User
    Join Date
    09-24-2019
    Location
    boston
    MS-Off Ver
    16.29
    Posts
    6

    Re: Conditional Formatting and Sum if... I think!

    I attached an example excel file.

    Basically, on tab 2, I want to see if "close date_opp" is = to "Date" or + 7 days. If yes, then I want to sum the revenue in column C to the revenue on Tab 1. IF the unique Id is the same.

    Obviously i only included one row on slide two, but that's for certain purposes.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    09-24-2019
    Location
    boston
    MS-Off Ver
    16.29
    Posts
    6

    Re: Conditional Formatting and Sum if... I think!

    Full transparency, I'm not great in excel so you'll have to "dumb" everything down for me. Apologies in advance.

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Conditional Formatting and Sum if... I think!

    I am guessing that the following will do what you want.
    As to the conditional formatting for the date in column B use: =B2<=E2+7
    As to the sum:
    1. Add a helper column (F), which may be moved and/or hidden for aesthetic purposes, also populated using: =B2<=E2+7
    2. Populate column C on the '1' sheet using: =SUMIFS('2'!C$2:C$5,'2'!A$2:A$5,A2,'2'!F$2:F$5,TRUE)
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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. [SOLVED] Conditional Formatting based on another cells Conditional Formatting
    By chriskay in forum Excel General
    Replies: 4
    Last Post: 08-22-2019, 05:33 AM
  2. [SOLVED] Override conditional formatting (in general, without changing the conditional formatting)
    By Stormin' in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 11-21-2017, 07:15 AM
  3. Conditional Formatting Removing Previous Conditional Formatting?
    By CravingGod in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-04-2016, 01:02 PM
  4. Replies: 6
    Last Post: 01-08-2016, 06:44 PM
  5. Opening xlsm files with conditional formatting opens with removed conditional formatting
    By Martijn.Steenbakker in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-07-2014, 05:38 AM
  6. Replies: 1
    Last Post: 09-20-2013, 06:23 PM
  7. Replies: 3
    Last Post: 05-15-2012, 04:13 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