Conditional Formatting - Highlight if I show no sales for 3 months

[davezieniewicz]

Hello all!

Can someone please help me find a conditional formatting formula for the following scenario?

I have a list of customers in column A.
Cells B2:M20 contains the list of monthly sales and some are blank. The blank cells represent no sales for that particular customer for that month.

If I have had sales with a customer one month, and then they show no sales for 3+ months thereafter, I want to highlight the 3 or more cells that show no sales.

Example:
The customer in cell A2 has monthly sales in each cell for row #1. B2 = January sales, C2 = February sales, D2 = March sales, etc.
If A2 had sales in January(B2) and the next 3 cells to the right(C2,D2,E2) are all blank, I want all three of those cells highlighted.

Additionally if they have more than 3 months with no sales, I need those highlighted as well.

I think you all in advance for the replies.

-Dave

 

[VoG]

Duplicate of http://www.mrexcel.com/forum/excel-...-if-i-show-no-sales-3-months.html#post3434550

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule #9 here: Forum Rules).

 

[davezieniewicz]

I just wanted to better clarify my situation:
I need a formula that will highlight a cell in column A for the following scenario:

If a customer has a sale, and then they have zero sales for 3 consecutive cells, I need their name highlighted.

Example:
[TABLE="class: cms_table, width: 825"]
<tbody>[TR]
[TD]*[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Watkins IV[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]8,000[/TD]
[TD]4,000[/TD]
[TD][/TD]
[TD]4,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]McCormack[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3,000[/TD]
[TD]11,466[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Lapinsky[/TD]
[TD]3,850[/TD]
[TD][/TD]
[TD][/TD]
[TD]3,850[/TD]
[TD][/TD]
[TD]3,850[/TD]
[TD][/TD]
[TD]3,850[/TD]
[TD][/TD]
[TD]3,850[/TD]
[/TR]
</tbody>[/TABLE]


Watkins IV does not have a sale until column G so this would not be a trigger to have his name highlighted since he had his first sale in column G and then he has not had 3 consecutive blank cells thereafter.

McCormack had their first sale in column F, then again in column G, but then you will see that he has 3 consecutive cells that are blank afterwards. I would need this to trigger his name to be highlighted.

Lapinski never had 3 consecutive blank cells so his name would not be highlighted.

Can someone please send me a conditional formatting formula to have the customer name highlighted in the case that there is 3 blank cells in a row after the initial sale?

Thank you all in advance for the replies. This one is extremely tough for me to figure out.

-Dave