# Thread: Help with arrays couning dates

1. ## Help with arrays couning dates

In the attached spread sheet on Sheet1 I have a chart that is to display the number of records per month. The numbers all work as they should except for September and November.

Can anyone tell me why these two are incorrect? This is my first experience with arrays.

Additionally, I had to use Countif on the "Prior to 2012" and "Post 2012" columns to get it to count the number of records.

TIA,

Bret

2. Hi Bret

I am finding your sheets strange, something to do with the dates, they are UK in 1 and US in the other.

I tried 2 formulas that should work, but they are throwing back strange results. Have you cut and pasted this workbook.
The formulas that I have used are and should work:

1: =SUMPRODUCT(--(MONTH(Sheet2!\$F\$5:\$F\$178)=MONTH(C\$3))) in C4 and copy across.

2: =SUM(IF(MONTH(Sheet2!\$F\$5:\$F\$178)=MONTH(C\$3),1)) in C4 and copy across. This is an array formula ctrl + shift + enter

Both return the same results: 3 1 14 6 5 2 5 2 18 35 7 76

The count is the same as the rows of the data 174, but shows 3 no entries for Jan-2012!

What are the values in C2 & D2!

Kevin

3. All the months are calc wrong as they will include any days from the 1st day of the next month. In your dataset, Oct 1 and Dec 1 are dates so they are counted for Sept and Nov. Use the formula:
=SUMPRODUCT((Sheet2!\$F\$5:\$F\$239>=M2)*(Sheet2!\$F\$5: \$F\$239<N2))

You will also have to change O2 to 1/1/2013 to keep the pattern and December accurate.

You could also use something like:
=SUMPRODUCT((YEAR(Sheet2!\$F\$5:\$F\$239)=YEAR(D3))*(M ONTH(Sheet2!\$F\$5:\$F\$239)=MONTH(D3)))

Which only uses one value from the same column.

Steve

4. @ sdckapr

I will hold my hands up to that, I "Missed the "YEARS"!!

@ Gasman
I have just down loaded the file this morning and can now see the data in C2:O2! Strange that one.You can do away with that row, save space.
Pull the years and months from what you already have in C3:N3

=SUMPRODUCT(--(YEAR(Sheet2!\$F\$5:\$F\$178)=YEAR(C\$3)),--(MONTH(Sheet2!\$F\$5:\$F\$178)=MONTH(C\$3))) and copy across.

Kevin

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•