Hello,
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 2010
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 |
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","") |