Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Jan 2009
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
    Attached Files Attached Files

  2. #2
    Star Lounger
    Join Date
    Oct 2012
    Posts
    55
    Thanks
    1
    Thanked 10 Times in 10 Posts
    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
    Last edited by Kevin@Radstock; 2012-11-16 at 13:43.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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
    Last edited by sdckapr; 2012-11-16 at 16:05.

  4. #4
    Star Lounger
    Join Date
    Oct 2012
    Posts
    55
    Thanks
    1
    Thanked 10 Times in 10 Posts
    @ 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
    Last edited by Kevin@Radstock; 2012-11-17 at 02:20.

Posting Permissions

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