Consulting

Results 1 to 7 of 7

Thread: More help with SumProduct

  1. #1
    VBAX Regular
    Joined
    Jun 2005
    Posts
    51
    Location

    More help with SumProduct

    Hello everyone,

    XLD was kind enough to help me with a Sumproduct problem a while ago. I am now trying to do an inspection profiling table where I think I need to use the same function again, but am having trouble adapting the formula to a different problem, and need more help.

    Basically in the attached sanitised workbook, I am generating a series of inspection dates for 'NORATE and UNRATE' inspection types in columns AA:BJ.

    CU2:CX18 contains a table, in which Column CW should have all the 'NORATE and UNRATE' inspections profiled for any given month that fall in the year defined by the dates in CW3 and CW4, ie between 01/04/2009 and 31/03/2010.

    Please look at my formula in CW6 which is supposed to show all the inspections that fall in January between 01/04/2009 and 31/03/2010. I have also defined a named range which is being used in the formula. I don't know whether there is a problem with the formula because of the way I am nesting the MONTH and AND functions within SUMPRODUCT or if the error is in the way I have defined the named range.

    Any advice would be much appreciated.

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Is this what you need?

    =SUMPRODUCT(--(TEXT(NoRate_UnRate_Inspection_Dates,"mmmm")=$CU6),
                         --(NoRate_UnRate_Inspection_Dates>$CW$3),
                         --(NoRate_UnRate_Inspection_Dates<$CW$4))
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Regular
    Joined
    Jun 2005
    Posts
    51
    Location
    Thanks xld,

    I tried your formula and the value in CW6 evaluates to 0. I know that there is at least 1 instance of an inspection in January 2010 because of the date in AA28.

    Could it be an error in my defined name range? I did a lot of fiddling around with that and am not confident I've defined it properly as don't fully understand about offsetting

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    I forgot to mention that your dynamic range definition point to offset 36 column, it should be 25, but this still gives 0 for January.

    Where is the item that should be counted?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    VBAX Regular
    Joined
    Jun 2005
    Posts
    51
    Location
    The dates to be counted are in columns AA to BJ. Just glancing at the data visually shows at least AA28, AB11, AC10 should be included in the January 2010 count.

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Ach! I see it now.

    I was mis-reading the dynamic range formula, I thought you were starting at A6, but you are starting at AA6.

    Change that formula to

    =OFFSET(Inspections!$AA$6,,,COUNTA(Inspections!$AA:$AA)-6,36)
    and you should be good.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  7. #7
    VBAX Regular
    Joined
    Jun 2005
    Posts
    51
    Location
    Thank you very much xld - that seems to have solved it.

Posting Permissions

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