# Conditional Formatting and Sum if... I think!

1. ## 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. ## 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. ## 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. ## 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.

5. ## 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. ## 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.

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