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:

*	A	B	C	D	E	F	G	H	I	J	K
1	Watkins IV						8,000	4,000		4,000
2	McCormack					3,000	11,466
3	Lapinsky	3,850			3,850		3,850		3,850		3,850

<tbody>
</tbody>

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