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

 

[rex759]

right but if I change A11 to 2/22, I don't want to see anything.

 

[admiral100]

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

You asked to see only those greater than 3, and 2/22/16 is equal to 3, so why you expect to see "<3"?

 

[admiral100]

" anything" you mean also in C2 & C10 ?
Do you want to check if A11 > 3 then C2 & C3 & C10 will show ">3" ?

 

[rex759]

I know, confusing isnt it. If it's greater than 3, then I want to flag all 4 (or more) dates that is involved even though some of the dates may not be over the 15 days. Yes to the second question, I dont want to see any errors in C2-C10.

 

[admiral100]

Try this , change the red value to your last cell

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

 

[rex759]

Looking at it again, I see its not logical to get the results that I want. Looking at the below example, is there a way to show the results I listed under "Dates involved"?

Excel Workbook
	A	B	C	D	E	F	G	H
1	Date	Mode	Error	#	Dates involved		Range (-15)	Date Range (+15)
2	2/1/16	1		3			1/17/16	2/16/16
3	2/10/16	1	>3	4	2/1, 2/10, 2/9 ,2/22		1/26/16	2/25/16
4	3/10/16	1		1			2/24/16	3/25/16
5	4/10/16	1		1			3/26/16	4/25/16
6	5/10/16	1		1			4/25/16	5/25/16
7	6/10/16	1		1			5/26/16	6/25/16
8	7/10/16	1		1			6/25/16	7/25/16
9	1/11/16	1		1			12/27/15	1/26/16
10	2/9/16	1	>3	4	2/1, 2/10, 2/9, 2/22		1/25/16	2/24/16
11	2/22/16	1		3			2/7/16	3/8/16
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","")
D2	=COUNTIFS($A$2:$A$11,">"&A2-15,$A$2:$A$11,"<"&A2+15,$B$2:$B$11,"="&B2)
G2	=SUM(A2-15)
H2	=SUM(A2+15)

 

[admiral100]

If you want that all values will be in one cell, it's a VBA task...

You can try this in I2 and copy down .....Control+Shift+Enter not just Enter
=IFERROR(INDEX($A$3:$A$12,SMALL(IF($D$3:$D$12>=3,ROW($D$3:$D$12)-2,""),ROW(A1)),1),"")

 

[admiral100]

Suited to your recent example
=IFERROR(INDEX($A$2:$A$11,SMALL(IF($D$2:$D$11>=3,ROW($D$2:$D$11)-1,""),ROW(A1)),1),"")

 

[rex759]

You beat to the punch. That works but I want it to list the results horizontal. I also added a different variable to the mix. In this instance, I want to show the site numbers involved if the corresponding cell in column D is >3. I thought I had it pulling the 2nd, 3rd & 4th occurrence but in this example, G3 should be 1120 and F10 should be 1120.

I appreciate your help in all of this.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	Date	Mode	Error	#	Sites	Sites	Sites	Sites		Range (-15)	Date Range (+15)
2	2/1/16	1		3	1111					1/17/16	2/16/16
3	2/10/16	1	>3	4	1112	1111	1118	1119		1/26/16	2/25/16
4	3/10/16	1		1	1113					2/24/16	3/25/16
5	4/10/16	1		1	1114					3/26/16	4/25/16
6	5/10/16	1		1	1115					4/25/16	5/25/16
7	6/10/16	1		1	1116					5/26/16	6/25/16
8	7/10/16	1		1	1117					6/25/16	7/25/16
9	1/11/16	1		1	1118					12/27/15	1/26/16
10	2/9/16	1	>3	4	1119	#VALUE!	1111	1112		1/25/16	2/24/16
11	2/22/16	1		3	1120					2/7/16	3/8/16
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","")
D2	=COUNTIFS($A$2:$A$11,">"&A2-15,$A$2:$A$11,"<"&A2+15,$B$2:$B$11,"="&B2)
J2	=SUM(A2-15)
K2	=SUM(A2+15)

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

 

[rex759]

Ok, I think I have it. I changed the array in F2 to =IF($D2>3,INDEX($E$2:$E$25,SMALL(IF($D$2:$D$25>=3,ROW($D$2:$D$25)-ROW($D$2)+1,ROW($D$25)+1),1),1),"")