Check for day range and no

问题:

I have the following data sheet:

As you can see I have a date and a no which is a string.

Now I want to filter out all dates which have the same number in a +-20 day intervall around the date. So for example:

If the 12.2.2014 has the value a then it gets a 1 and if the 3.2.2014 has the value a it gets also a 1. In contrast if the value 15.1.2014 has the value a it gets a 0 because it is not in the +-20 day range. If there exists two rows like in the example below with 10.07.2002 and value d then it gets a 0 because there is no other day in a 20 day intervall around it.

My implementation idea is: Calculate for each value and date the difference of the dates and if it is less or equal than 20 and greater and equal to 1 then give out a 1 else a 0.

Can this be done in excel?

I really appreciate any suggestions

PS.: I hope that I showed the problem clearly!

UPDATE

Using this formula I get:

=COUNTIFS($B$2:$B$32;B2;$A$2:$A$32;">"&(A2-20);$A$2:$A$32;"<"&(A2+20))-1

see col E.

回答1:

Something like this (adjust the $B$2:$B$32 etc to your actual data range):

 =COUNTIFS($B$2:$B$32,B2,
                $A$2:$A$32,">" & (A2-20),
                $A$2:$A$32,"<" & (A2+20))-1

-1 is to avoid counting the row the formula is on.

To give only 0 or 1:

 =MIN(COUNTIFS($B$2:$B$32,B2,
                $A$2:$A$32,">" & (A2-20),
                $A$2:$A$32,"<" & (A2+20))-1,1)