Countifs Results Problem

[rex759]

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

 

[admiral100]

Hi,

You can add "=" to the formula
=IF(COUNTIFS($A$2:$A$11,">"&A2-15,$A$2:$A$11,"<"&A2+15,$B$2:$B$11,"="&B2)>=3,">3","")

 

[rex759]

Hello and thank you for responding. This is much closer but if it only = 3, I don't want the message to appear. I only want to see the message if >3.

 

[admiral100]

For C11 (2/22/16) there are only 3 dates that return TRUE for your formula....and that is why you don't see the “>3”

 

[rex759]

Correct but I don't want to see the rest of the ">3"if it only equals 3. Does that make sense? The error should truly show when there is 4 or more.

 

[rex759]

For example, if A11 = 2/22, I should see any errors. If A11= 2/15, the error should show in C2, C3, C10 & C11

 

[rex759]

*correction
For example, if A11 = 2/22, I shouldn't see any errors. If A11= 2/15, the error should show in C2, C3, C10 & C11

 

[admiral100]

Date	Mode	Error	Nmber of
01/02/16	1		3
10/02/16	1	>3	4
10/03/16	1		1
10/04/16	1		1
10/05/16	1		1
10/06/16	1		1
10/07/16	1		1
11/01/16	1		1
09/02/16	1	>3	4
22/02/16	1		3

<colgroup><col width="70" span="4" style="width:52pt"> </colgroup><tbody>
</tbody>

As you can see only if it's >3 it show the ">3"

 

[rex759]

So in your example, I would want to see the 4 cells that make up that red 4. Maybe countifs isn't the way to go

 

[admiral100]

Date	Mode	Error	Number of
01/02/16	1	>3	4
10/02/16	1	>3	4
10/03/16	1		1
10/04/16	1		1
10/05/16	1		1
10/06/16	1		1
10/07/16	1		1
11/01/16	1		1
09/02/16	1	>3	4
15/02/16	1	>3	4

<tbody>
</tbody>

If you chnage A11 to 2/15 then 4 cells are >3