Consulting

Results 1 to 5 of 5

Thread: Sumproduct/Array formula for cells with date

  1. #1
    VBAX Regular
    Joined
    Dec 2008
    Posts
    85
    Location

    Sumproduct/Array formula for cells with date

    Hi all:

    I have this data:

    A Date----------##-------Total January-----Total November
    1/12009---------10---------14----------------5
    11/3/2009-------5
    1/4/2009--------4
    -----------------3
    -----------------7

    Arrray enter = (month(A1:A5)=1)*(B1:b5) = 24 and not 14 since a month of a blank cell is 1 (month 1/0/1900).

    Any idea how I can solve this situation using arrays.

    Thanks for the help.

    Victor

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,446
    Location
    Try

    =SUMPRODUCT(--(A1:A5<>""),--(MONTH(A1:A5)=1),B1:B5)
    ____________________________________________
    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
    Dec 2008
    Posts
    85
    Location
    xld
    Can you explain the use of — before the test, please ?
    thanks
    Victor

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,446
    Location
    See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed explanation.
    ____________________________________________
    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
    Dec 2008
    Posts
    85
    Location
    XLT

    Nice, "you can coerce to 1/0 with the double unary --" instate of multiply by cero for one condition.

    Thanks

    Victor

Posting Permissions

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