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

    sum; count (2000)

    I think this will be an easy one: I have 30+ years of daily quantities of a commodity I have purchased. i would like to sum all of the purchases, say, that I made on February 10th of all of the years, and then count the number of years that I purchased the commodity on the date. The format of my worksheet is in Column A are dates (each day) in "mm/dd/yyyy" format, and Column B, the quantity purchased on that date. I have tried Month() and Day(), but can't seem to get it to do what I want.

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

    Re: sum; count (2000)

    Sorry-didn't work...

  3. #3
    Star Lounger
    Join Date
    Feb 2004
    Location
    Jacksonville, Florida
    Posts
    70
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sum; count (2000)

    I'm not sure for the cumulative yr, however for the quantity, could you make a column c: =month(A1)&"/"day(a1), then fill that down the page, and on another sheet have column a1= 02/10, and a2=sumif(sheet1!c$:c$,a1,sheet1!b$:$[img]/forums/images/smilies/cool.gif[/img] that way it looks mm/dd you created, and if that matches what is in sheet2!a1, then it will sum up the purchases. Then just fill in the dates across the row that you're calculating for and fill the formula- is that what you're looking for? I'm no guru by any means, but that's what I gathered from your question.

  4. #4
    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: sum; count (2000)

    Jeff,

    Wasn't this already "asked and answered" in <post#=339452>post 339452</post#>? And a similar one on commodities in <post#=315619>post 315619</post#>.

    A very similar variation was the temperature averages (which are solved by a similar answer in <post#=227625>post 227625</post#> and even <post#=337738>post 337738</post#>?

    There is even some coding done to get rid of the "sluggishness" of the arrays in <post#=316558>post 316558</post#>.

    Steve

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

    Re: sum; count (2000)

    Steve,
    Absolutely. You are correct-I hads forgotten about 337738, et seq., and it didn't even show up in a search.
    Thanks, and sorry!
    Jeff

  6. #6
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sum; count (2000)

    You can search on questions you have asked in the past by choosing New Topic by Username - as shown in the attached. HTH
    Gre

Posting Permissions

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