Count Values in A Range of Dates

[billandrew]

Good morning All

Actually viewed this subject in a recent Post. Attempting to understand why the Int Function returns a value error from a cell. I believe it has something to do with the dates not in English format.

The cells with the error are from 16/01/2017 11:30 and down.[TABLE="width: 397"]
<tbody>[TR]
[TD]Thanks[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Example

[TABLE="width: 397"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Order[/TD]
[TD]Date[/TD]
[TD]Item[/TD]
[/TR]
[TR]
[TD]3168-5252[/TD]
[TD]2/1/2017 9:30[/TD]
[TD]448[/TD]
[/TR]
[TR]
[TD]3168-5252[/TD]
[TD]2/1/2017 9:30[/TD]
[TD]448[/TD]
[/TR]
[TR]
[TD]3321-5464[/TD]
[TD]2/1/2017 10:30[/TD]
[TD]441[/TD]
[/TR]
[TR]
[TD]3412-5611[/TD]
[TD]2/1/2017 10:30[/TD]
[TD]508[/TD]
[/TR]
[TR]
[TD]3412-5612[/TD]
[TD]2/1/2017 10:30[/TD]
[TD]508[/TD]
[/TR]
[TR]
[TD]3412-5612[/TD]
[TD]2/1/2017 10:30[/TD]
[TD]508[/TD]
[/TR]
[TR]
[TD]3412-5612[/TD]
[TD]2/1/2017 10:30[/TD]
[TD]508[/TD]
[/TR]
[TR]
[TD]3396-5585[/TD]
[TD]2/1/2017 11:00[/TD]
[TD]631[/TD]
[/TR]
[TR]
[TD]3396-5586[/TD]
[TD]2/1/2017 11:00[/TD]
[TD]631[/TD]
[/TR]
[TR]
[TD]3396-5586[/TD]
[TD]2/1/2017 11:00[/TD]
[TD]631[/TD]
[/TR]
[TR]
[TD]3519-5777[/TD]
[TD]16/01/2017 11:30[/TD]
[TD]239[/TD]
[/TR]
[TR]
[TD]3339-5498[/TD]
[TD]16/01/2017 12:00[/TD]
[TD]157[/TD]
[/TR]
[TR]
[TD]3541-5811[/TD]
[TD]16/01/2017 12:30[/TD]
[TD]508[/TD]
[/TR]
[TR]
[TD]3541-5812[/TD]
[TD]16/01/2017 12:30[/TD]
[TD]508[/TD]
[/TR]
[TR]
[TD]3541-5812[/TD]
[TD]16/01/2017 12:30[/TD]
[TD]508[/TD]
[/TR]
[TR]
[TD]3541-5812[/TD]
[TD]16/01/2017 12:30[/TD]
[TD]508[/TD]
[/TR]
[TR]
[TD]3541-5812[/TD]
[TD]16/01/2017 12:30[/TD]
[TD]508[/TD]
[/TR]
[TR]
[TD]3541-5812[/TD]
[TD]16/01/2017 12:30[/TD]
[TD]508[/TD]
[/TR]
[TR]
[TD]3495-5744[/TD]
[TD]16/01/2017 14:10[/TD]
[TD]448[/TD]
[/TR]
[TR]
[TD]3547-5819[/TD]
[TD]16/01/2017 14:30[/TD]
[TD]731[/TD]
[/TR]
[TR]
[TD]3548-5820[/TD]
[TD]16/01/2017 14:30[/TD]
[TD]732[/TD]
[/TR]
[TR]
[TD]3555-5829[/TD]
[TD]17/02/2017 09:30[/TD]
[TD]545[/TD]
[/TR]
[TR]
[TD]3567-5847[/TD]
[TD]17/02/2017 10:30[/TD]
[TD]255[/TD]
[/TR]
[TR]
[TD]3567-5848[/TD]
[TD]17/02/2017 10:30[/TD]
[TD]255[/TD]
[/TR]
[TR]
[TD]3567-5848[/TD]
[TD]17/02/2017 10:30[/TD]
[TD]508[/TD]
[/TR]
[TR]
[TD]3567-5848[/TD]
[TD]17/01/2017 10:30[/TD]
[TD]509[/TD]
[/TR]
[TR]
[TD]3567-5848[/TD]
[TD]17/02/2017 10:30[/TD]
[TD]255[/TD]
[/TR]
[TR]
[TD]3458-5683[/TD]
[TD]17/02/2017 13:00[/TD]
[TD]523[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

What is the question? You need of course to have date/time range in a way that is compatible with your system.

 

[billandrew]

attempting to sum the number of items from 1/1/2017 to 1/31/2017.

I am using the formula SUM(IF(FREQUENCY(IF(INT(B$2:B$29)>=F1,IF(INT(B$2:B$29)<=EOMONTH(F1,0),MATCH(C$2:C$29,C$2:C$29,0))),ROW(C$2:C$29)-ROW(C$2)+1),1)) cell F1 = the date 1/1/2017.

I am incurring a value error from dates starting at cell B12 ( 16/01/2017 ).

 

[Aladin Akyurek]

attempting to sum the number of items from 1/1/2017 to 1/31/2017.

I am using the formula SUM(IF(FREQUENCY(IF(INT(B$2:B$29)>=F1,IF(INT(B$2:B$29)<=EOMONTH(F1,0),MATCH(C$2:C$29,C$2:C$29,0))),ROW(C$2:C$29)-ROW(C$2)+1),1)) cell F1 = the date 1/1/2017.

I am incurring a value error from dates starting at cell B12 ( 16/01/2017 ).

Because we have to do with numbers...

Control+shift+enter, not just enter:

Either...

=SUM(IF(FREQUENCY(IF(INT(B2:B29)-DAY(INT(B2:B29))+1=F1,D2:D29),D2:D29),D2:D29))

Or...

=SUM(IF(FREQUENCY(IF(INT(B2:B29)>=F1,IF(B2:B29<=EOMONTH(F1,0),D2:D29)),D2:D29),D2:D29))

 

[Aladin Akyurek]

By the way, if you are intending to count, not to sum...

=SUM(IF(FREQUENCY(IF(INT(B2:B29)-DAY(INT(B2:B29))+1=F1,D2:D29),D2:D29),1))

=SUM(IF(FREQUENCY(IF(INT(B2:B29)>=F1,IF(B2:B29<=EOMONTH(F1,0),D2:D29)),D2:D29),1))

 

[billandrew]

My error, I am attempting to sum the number of dates which fall between 1/1/2017 & 1/31/2017. The issue is the dates are in the incorrect format.

Example 2/1/2017 9:30 is actually January 1, 2017 etc....

 

[Aladin Akyurek]

My error, I am attempting to sum the number of dates which fall between 1/1/2017 & 1/31/2017. The issue is the dates are in the incorrect format.

Example 2/1/2017 9:30 is actually January 1, 2017 etc....

The format is DMY + time. You need to convert it to MDY + time if you are on an American system. This must be done for each date/time entry.

 

[billandrew]

thank you for all the assistance.