Counting dates in a range

[wackybadger]

I am trying to count the number of dates that fall within certain months/years in a large range with a majority of blank cells (to be filled in the future). The data comprises nearly 700 rows and 35 columns and I would like to count the # of dates in every month from January 2009 to January 2019 and beyond. The data is contained in cells from AK6:BR699. I've tried various versions of SUMPRODUCT such as:

=SUMPRODUCT((MONTH(AK6:BR699)=AS732)*(YEAR(AK6:AK699)=AR733)) where AS732 and AS733 contain the month and year of interest, respectively, but due to all of the blank cells, I get #VALUE !. Any suggestions?

 

[Mentor82]

Hi,
Type in the formula as an array by pressing ctrl+shift+entre after typing in so you'll get additional brackets {...}, check the resultand let me know.

=SUMPRODUCT((MONTH($AK$6:$BR$699)=$AS732)*(YEAR($AK$6:$AK$699)=$AR733))

Regards,
Sebastian

 

[AhoyNC]

Don't think the blank cells are causing the problem. Sounds like you have some text values in the range which will cause the #VALUE error.
Try changing your formula to:
This is an array formula and must be entered with CTRL-SHIFT-ENTER.

[TABLE="width: 608"]
<colgroup><col width="608"></colgroup>[TR]
   [TD="width: 608"]SUM(IF(ISNUMBER(AK6:BR699),(MONTH(AK6:BR699)=AS732)*(YEAR(AK6:AK699)=AR733)))[/TD]
 [/TR]
[/TABLE]

See small example below. By any chance are all your dates in the same column?

Excel Workbook
	A	B	C	D	E	F
1					Month=	2
2	2/2/2018				Year =	2018
3			3/1/2018
4		4/5/2019	2/18/2018		Count =	2
5		2/19/2019	aaaa
Sheet

 

[wackybadger]

Don't think the blank cells are causing the problem. Sounds like you have some text values in the range which will cause the #VALUE error.
Try changing your formula to:
This is an array formula and must be entered with CTRL-SHIFT-ENTER.

[TABLE="width: 608"]
<tbody>[TR]
[TD="width: 608"]SUM(IF(ISNUMBER(AK6:BR699),(MONTH(AK6:BR699)=AS732)*(YEAR(AK6:AK699)=AR733)))[/TD]
[/TR]
</tbody>[/TABLE]

See small example below. By any chance are all your dates in the same column?

	A	B	C	D	E	F
1					Month=	2
2	2/2/2018				Year =	2018
3			3/1/2018
4		4/5/2019	2/18/2018		Count =	2
5		2/19/2019	aaaa

<colgroup><col style="width:30px; "><col style="width:64px;"><col style="width:68px;"><col style="width:68px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula F4 {=SUM(IF(ISNUMBER(A1:C5),(MONTH(A1:C5)=$F$1)*(YEAR(A1:C5)=$F$2)))} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Thank you for the suggestion of text in the range. I had put a bunch of X's all of the blank cells previously (in white font to be invisible) so that when sorting, nothing went amiss (which I've had happen before). X's removed, formula works beautifully. Thank you!

 

[AhoyNC]

Glad you got things worked out. The array formula I gave above should work if you put an x in the the blank cells.