• 513K Members
• 2,228 Online
• 610K Conversations
SOLVED

## Conditional formatting with custom formula: revenue value in last three months

Occasional Contributor

# Conditional formatting with custom formula: revenue value in last three months

Hi!

I've got a custom formula for conditional formatting I'd like some help with. This URL holds a copy example of my original file: https://docs.google.com/spreadsheets/d/1wp3AMnlZr0hgRBLbQlMBgHe-a-g3VQOeFiCEgVZaUwU/edit?usp=sharing.

It's a file with revenue data per customer per month. What I need is to highlight the last three months if there is no revenue or revenue under 200 EUR per month.

I've got the part covered on how to highlight the last three months with the following formula:
=AND(MONTH(U\$1)<MONTH(TODAY());MONTH(U\$1)>MONTH(TODAY())-4)

To check the revenue I added ;SUM(U2:W2)<350 to the formula, but this isn't right. As the capture shows, in row 2 it highlights the 0 EUR - even though this is obviously an active customer. Then in row 4, the months of April and May aren't highlighted even though they should be.. I also tried ;U2<200 but this too highlights individual cells and not the combination of the last three
months.

Any ideas on how I can get this part of the formula to work?

Thanks!
- Noortje
6 Replies

# Re: Conditional formatting with custom formula: revenue value in last three months

Hi Noortje,

If you mean to check if this year revenue is less from 350, i.e. from Jan to running month, when you have to fix Jan in formula by absolute reference:

`=AND(MONTH(U\$1)<MONTH(TODAY()),MONTH(U\$1)>MONTH(TODAY())-4,SUM(\$U2:W2)<350)`

# Re: Conditional formatting with custom formula: revenue value in last three months

Hi Sergei,

Thanks for your response! What I'm looking for is actually not fixed (i.e. revenue from Jan - now), but should adapt to always include the last three months (i.e. revenue from March - May at this point in time).

Meaning: at this point in time it should highlight March - May revenue if it is less than 350. If it were currently November, it should highlight August - October revenue if less than 350 combined (or <200 per month).

Is this possible?

# Re: Conditional formatting with custom formula: revenue value in last three months

Perhaps this illustrates better what I am looking for:

It's currently June, meaning that the 3 month period I'm looking at is March to May. I need these three months to be highlighted if every one of these months'revenue is below 200, or the combined revenue is below 350 (whichever works better in a formula).

So if March=0, April=500, May=10 then this shouldn't be highlighted (the revenue in April shows this is an active customer).

But if March=10, April=120, May=30 then these three months should be highlighted (this revenue does not show an active customer as the values are too low for significant purchases).
Solution

# Re: Conditional formatting with custom formula: revenue value in last three months

@Noortje Vollenberg , formula rule could be

`=((SUMPRODUCT( ((\$U\$1:\$AF\$1<EOMONTH(TODAY(),-1))*(\$U\$1:\$AF\$1>EOMONTH(TODAY(),-4)))*\$U2:\$AF2)<350)+(SUMPRODUCT( ((\$U\$1:\$AF\$1<EOMONTH(TODAY(),-1))*(\$U\$1:\$AF\$1>EOMONTH(TODAY(),-4)))*(\$U2:\$AF2<200))=3))*(U\$1<EOMONTH(TODAY(),-1))*(U\$1>EOMONTH(TODAY(),-4))`

# Re: Conditional formatting with custom formula: revenue value in last three months

Hi Sergei,

Thanks so much for your help. This is working perfectly!

I've added a second variation of the formula with AND( .... ;U2=0) to hide the text for cells with a value of 0 so the file is more legible.

# Re: Conditional formatting with custom formula: revenue value in last three months

I see, didn't think about all zeroes.

Related Conversations
Conditional Formatting - Consider Only Value as Lowest Value
Ben Smith in Excel on
2 Replies
formula for data calculation
aayushman_mishra in Excel on
10 Replies
Creating A Sublist
zjohnson in Excel on
5 Replies
Excel Forumla to exclude empty cells.
ulken2019 in Excel on
6 Replies
conditional formatting with formula
kdwork in Excel on
3 Replies