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.

Thanks

<tbody>
</tbody>

Example

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

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[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.