Dynamic count by month over several years - excel 2016

[dave bates]

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Received
[/TD]
[TD]Sent
[/TD]
[TD][/TD]
[TD]Jan 08
[/TD]
[TD]Feb 08
[/TD]
[TD]March 08
[/TD]
[TD]Apr 08
[/TD]
[TD]May 08
[/TD]
[TD]Jun 08
[/TD]
[TD]July 08
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]28 Jan 2008
[/TD]
[TD]5 Dec 2008
[/TD]
[TD]Received
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]1
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]31 July 2008
[/TD]
[TD]12 April 2008
[/TD]
[TD]Sent
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]13 September 2008
[/TD]
[TD]1 March 2008
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]20 November 2008
[/TD]
[TD]2 July2008
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]1 Oct 2008
[/TD]
[TD]2 June 208
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]etc
[/TD]
[TD]etc
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Sorry but I cant get the table lower.

I have the following formula that somebody previously gave me for counting he number of date entries that fall between 2 dates (apologies but I can't remember who)

Dates are in columns A and starting from row 2.
The date ranges are in row 1 starting from D1

The formula works great.

I want to convert the dates range from being in row to being in a column beneath the data.

I'm stuck and would welcome any help the range in column A (when move) will be subject to change so the formula needs to reference the cell date.

the current formula in cells D2 to J2 is {=SUM((MONTH($A2:$A11)=COLUM()-3*1)}

what is currently D1 to J1 will beA15 to A22

thanks

 

[James006]

Hi,

You could test following array formula in cell A15

=SUM((MONTH(A$2:A$11)=ROW()-14)*1)

Hope this will help

 

[dave bates]

many thanks

the layout is not exactly as my question

the dates to review are in column B starting in B9

the ranges will be in column B stating in row 28
I have tried the above with { and adjusting the -14 but it returns 0. Is the -14 a count from the row of the calculation, in the new layout -27.

it does work with the -27 however the first line collection, the Jan 08 also picks up blanks in the range A2:A11 (but which I have corrected)

is it best to extend the formula to deduct he count of blank cells

sorry for the confusion and lack of clarity