Results 1 to 3 of 3
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    sumproduct (2000)

    In column A I have 6 months listed in rows 1-6 (entered as mm/dd/yy), in the format mmm-yy. In column B, I have quantities of a product purchased each day of the respective month. What I want to do is enter a function (sumproduct maybe?) in cell B7 that sums the product of the individual months purchases. However whenever I try and enter "day(eomonth(a1:a6,0))" to calculate the days, I get a "Name" error.
    Help?

  2. #2
    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

    Re: sumproduct (2000)

    Name error means that excel can not find a function.
    EOMonth is part of the "Analysis Toolpack addin". I would speculate that you have not installed it. to do this follow the directions under HELP for EOMONTH

    I figured that:
    <pre>=SUMPRODUCT(DAY(EOMONTH(A1:A6,0)),B1:B6)</pre>


    is what you are looking for
    BUT this does not work!!
    EOMonth does not seem "set up" to work with an "array" of values, (it is an array that is not in an array formula: it does not even work in a "proper" array formula at all: at least in XL97)


    You can "simulate' an day(eomonth) type of formula and do the sumproduct with [Note: this is normal formula, confirm with just <enter>]
    <pre>=SUMPRODUCT(DATE(YEAR(A1:A6),MONTH(A1:A6)+1,1 )-DATE(YEAR(A1:A6),MONTH(A1:A6),1),B1:B6)</pre>


    Steve

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: sumproduct (2000)

    Thanks, Steve; as usual, works like a charm!
    Happy New Year!

Posting Permissions

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