I want to show the message “>3” for all dates involved if the date inputted falls between a 30 day period.

In the example below, I want the dates 2/1/16 & 2/22/16 to also show the message “>3” because they are part of the 4 dates that is causing the error.

Is there a better formula to show all dates involved than countifs to obtain these results?

Excel Workbook | |||||
---|---|---|---|---|---|

A | B | C | |||

1 | Date | Mode | Error | ||

2 | 2/1/16 | 1 | |||

3 | 2/10/16 | 1 | >3 | ||

4 | 3/10/16 | 1 | |||

5 | 4/10/16 | 1 | |||

6 | 5/10/16 | 1 | |||

7 | 6/10/16 | 1 | |||

8 | 7/10/16 | 1 | |||

9 | 1/11/16 | 1 | |||

10 | 2/9/16 | 1 | >3 | ||

11 | 2/22/16 | 1 | |||

Sheet1 |

**Excel 2010**

Cell Formulas | ||
---|---|---|

Range | Formula | |

C2 | =IF(COUNTIFS($A$2:$A$11,">"&A2-15,$A$2:$A$11,"<"&A2+15,$B$2:$B$11,"="&B2)>3,">3","") | |

C3 | =IF(COUNTIFS($A$2:$A$11,">"&A3-15,$A$2:$A$11,"<"&A3+15,$B$2:$B$11,"="&B3)>3,">3","") | |

C4 | =IF(COUNTIFS($A$2:$A$11,">"&A4-15,$A$2:$A$11,"<"&A4+15,$B$2:$B$11,"="&B4)>3,">3","") | |

C5 | =IF(COUNTIFS($A$2:$A$11,">"&A5-15,$A$2:$A$11,"<"&A5+15,$B$2:$B$11,"="&B5)>3,">3","") | |

C6 | =IF(COUNTIFS($A$2:$A$11,">"&A6-15,$A$2:$A$11,"<"&A6+15,$B$2:$B$11,"="&B6)>3,">3","") | |

C7 | =IF(COUNTIFS($A$2:$A$11,">"&A7-15,$A$2:$A$11,"<"&A7+15,$B$2:$B$11,"="&B7)>3,">3","") | |

C8 | =IF(COUNTIFS($A$2:$A$11,">"&A8-15,$A$2:$A$11,"<"&A8+15,$B$2:$B$11,"="&B8)>3,">3","") | |

C9 | =IF(COUNTIFS($A$2:$A$11,">"&A9-15,$A$2:$A$11,"<"&A9+15,$B$2:$B$11,"="&B9)>3,">3","") | |

C10 | =IF(COUNTIFS($A$2:$A$11,">"&A10-15,$A$2:$A$11,"<"&A10+15,$B$2:$B$11,"="&B10)>3,">3","") | |

C11 | =IF(COUNTIFS($A$2:$A$11,">"&A11-15,$A$2:$A$11,"<"&A11+15,$B$2:$B$11,"="&B11)>3,">3","") |