Count values that only occur in same month and year

[crimewatcher]

I keep track of the state's lottery numbers. Trying to figure out how to count all the times the same number was drawn in the same month and year. A simple =COUNTIF(D:D,"<31") works but I don't want the Days Between counted if the same numbers were drawn in different months. Say 123 was drawn on 1/25/10 and again on 2/12/10. There are 18 days between these two dates but the =COUNTIF above counts it.

Is there an easy way to calculate or count the Days Between cells that are less than 31 and ONLY if the month and year are the same?

The file looks like this:

Number Date1 Date2 Days Between (Date2-Date1)

106 02/09/10 02/23/10 14
290 01/03/10 01/21/10 18
711 06/05/10 06/13/10 8
717 02/10/10 02/16/10 6
809 01/04/10 01/18/10 14
853 01/11/10 01/13/10 2

Thanks!

 

[Aladin Akyurek]

What is the expected result for the sample you posted?

 

[crimewatcher]

What is the expected result for the sample you posted?

The sample is what I'm looking for. I manually remove the Days Between that are less than 31 if the months are different. Sorry for the confusion - I should have put the 123 example in the middle like this:

Number Date1 Date2 Days Between (Date2-Date1)

106 02/09/10 02/23/10 14

123 01/25/10 02/12/10 Either don't count or don't calculate the Days Between - different months.

290 01/03/10 01/21/10 18
711 06/05/10 06/13/10 8
717 02/10/10 02/16/10 6
809 01/04/10 01/18/10 14
853 01/11/10 01/13/10 2

Thanks!

Tom

 

[Aladin Akyurek]

Please create and post a 8 row sample, state the criterion, and the expected result
in order to avoid any confusion...

 

[crimewatcher]

Please create and post a 8 row sample, state the criterion, and the expected result
in order to avoid any confusion...

Number Date1 Date2 Days Between (=Date2-Date1)

106 02/09/10 02/23/10 14 Expected result. Days less than 31 and months are the same.

123 01/25/10 02/12/10 Don't count or calculate the Days Between - different months.

290 01/03/10 01/21/10 18 Expected result. Days less than 31 and months are the same.

711 06/05/10 06/13/10 8 Expected result. Days less than 31 and months are the same.

717 02/10/10 02/16/10 6 Expected result. Days less than 31 and months are the same.

809 01/04/10 01/18/10 14 Expected result. Days less than 31 and months are the same.

853 01/11/10 01/13/10 2 Expected result. Days less than 31 and months are the same.

902 01/31/10 02/25/10 Don't count or calculate the Days Between - different months.

 

[crimewatcher]

Please create and post a 8 row sample, state the criterion, and the expected result
in order to avoid any confusion...

Here's a link to the sample file

http://gozips.uakron.edu/~evert/DaysBetween.xlsx

 

[Aladin Akyurek]

Try...

Control+shift+enter, not just enter:

=SUM(IF($B$3:$B$10-DAY($B$3:$B$10)+1=
    $C$3:$C$10-DAY($C$3:$C$10)+1,
      IF($C$3:$C$10-$B$3:$B$10<31,1)))

Is this what you wanted?

 

[crimewatcher]

Try...

Control+shift+enter, not just enter:

=SUM(IF($B$3:$B$10-DAY($B$3:$B$10)+1=
    $C$3:$C$10-DAY($C$3:$C$10)+1,
      IF($C$3:$C$10-$B$3:$B$10<31,1)))

Is this what you wanted?

Yes. THANK YOU!!!!