Dynamic count by month over several years - excel 2016

[dave bates]

	A	B	C	D	E	F	G	H	I	J
1	Received	Sent		Jan 08	Feb 08	March 08	Apr 08	May 08	Jun 08	July 08
2	28 Jan 2008	5 Dec 2008	Received	1	0	0	1	0	1	3
3	31 July 2008	12 April 2008	Sent	0	0	2	1	0	2	1
4	13 September 2008	1 March 2008
5	20 November 2008	2 July2008
6	1 Oct 2008	2 June 208
7	etc	etc
8

<tbody>
</tbody>

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