Announcement

Collapse
No announcement yet.

Sum by variable date criteria

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Sum by variable date criteria



    Hello, I have been looking around for a way to do this and haven't quite found what I am looking for. I have a list of entries, each with a date and a number associated with that date. Currently, these dates are stored as Excel dates, and I would like to keep it that way if possible. What I am trying to do is do something like this "=SUMIF(Sheet2!A2:A16, "1/1/2009",Sheet2!B2:B16)", but instead of it needing to be the first of the month, I would like to have any day of the month be valid. However, =SUMIF(Sheet2!A2:A16, "1/*/2009",Sheet2!B2:B16) does not work, likely due to the value actually being a simple date number.

    I imagine this is possible using SUM(IF()), but I could not figure it out.

    Any help would be greatly appreciated, and I have attached a simple Excel sheet example that might explain better what I am trying to do.

    -Kevin
    Attached Files
    Last edited by AAE; May 25th, 2010, 04:37. Reason: Amend thread title

  • #2
    Re: Sum by variable date criteria

    shrodekill,

    Welcome to Ozgrid.

    Your thread title shoud reflect your need and not an anticipated or assumed solution. I have change the title for you. Good thread titles are key to obtainin relevant search results, thus the need to be precise and not assumptive of the answer.

    Have you considered a Pivot Table as a possible solution? SUMPRODUCT may also be viable here.
    AAE
    ----------------------------------------------------

    Forum Rules | Message to Cross Posters | How to use Tags

    Comment


    • #3
      Re: Sum by variable date criteria

      Change the Text format to Date format before passing the criteria, and then use the following array formula

      {=SUM((MONTH(Sheet2!$A$2:$A$16)=MONTH(Sheet1!A2))*(Sheet2!$B$2:$B$16))}

      There you go....
      Attached Files
      Cheers,

      S M C

      Click To Read: How To Use Tags In Your Threads/Posts
      Please take time to read Forum Rules before posting
      Message To Cross Posters

      Comment


      • #4
        Re: Sum by variable date criteria

        Ah, thank you very much AAE. I never thought about using SUMPRODUCT with boolean values before.

        =SUMPRODUCT(Sheet2!B2:B16*(MONTH(Sheet2!A2:A16)=*insert month # here*)) is exactly what I needed.

        Comment


        • #5


          Re: Sum by variable date criteria

          Oh wow, thank you as well SMC for the quick answer. Both of these solutions work wonders.

          Thanks again

          Comment

          Working...
          X