Announcement

Collapse
No announcement yet.

If Cell Date a Certain Month Then....

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • If Cell Date a Certain Month Then....



    I am trying to develop a SUMIF formula that looks up a date mmddyyyy and only keys off the month in the range cells. Example: =SUMIF(B7,"August",E7:F7)
    B7 has the full date in ie. 8/10/2007 or 10-Aug-2007. My work around has been to separate out the date, month, and year into three different columns. Can you define the date by a range? Example:=IF(AND(B6>=8/1/2007,B6<=8/31/2007),SUM(E6:F6), 0) ... this doesn't work, but I was hoping I was on the right trail.

    Help!

  • #2
    Re: If Formulas With Dates In The Criteria

    Try:

    =IF(AND(B6>="8/1/2007",B6<="8/31/2007"),SUM(E6:F6), 0)
    .

    Comment


    • #3
      Re: If Formulas With Dates In The Criteria

      Could you post a small sample so we can see how things are layed out? I would probably use SumProduct.

      Comment


      • #4
        Re: If Formulas With Dates In The Criteria


        Try this formula. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas
         
         =IF(MONTH(B6)=8,SUM(E6:F6),0) 

        Comment


        • #5
          Re: If Cell Date a Certain Month Then....

          Attached is a picture file copy of the spreadsheet I am working from.

          It shows the day month and year in three separate columns. This is a work around to get the formula to look up the month. I would like to have one date field that the formula looks at. But Excell keeps translating the date into a number, therefore the formula can look up "august".

          =IF(AND(B6>="8/1/2007",B6<="8/31/2007"), SUM(E6:F6), 0) Didn't work.

          =IF(B5="August",SUM(E5:F5), 0) is the work around formula.

          =IF(B8="August",SUM(E8:F8), 0) this is another work around formuala having the day month and year in separate columns.

          Again, I am looking to have on date column in this spread sheet.
          Thanks for everyone's help

          Comment


          • #6
            Re: If Cell Date a Certain Month Then....

            Dave's formula works for me.

            Comment


            • #7
              Re: If Cell Date a Certain Month Then....

              Turtle 44 and Dave,

              The formula works for me, not sure what happened the other day. but it works for me now. Thanks a bunch!

              Comment


              • #8
                Re: If Formulas With Dates In The Criteria

                I know it's a thread that hasn't been updated in awhile, but I got a similar issue and so far haven't managed to get a solution to this.
                Namely I have an Excel file with a sheet of data originating from a consumer survey (names, dates, ratings for questions) and another sheet where I'm supposed to show some summary based on the data.
                For some stuff it was quite straightforward but when it comes to giving a table where I should show the average rating for one of the questions divided per month then I'm starting to run into problems.
                So far here's what I managed to come up with:

                =AVERAGE(IF((TEXT(surveys!$I$2:$I$60000,"yyyymm")=TEXT(C$60,"yyyymm")),(surveys!$N$2:$N$60000),FALSE))
                of course I enter it as an array formula, but for whatever reason it doesn't really give me always a correct answer. I would need to use the formula for five other questions (consequently columns) as well and I was wondering if there is a simpler way to do this.
                C$60 has the date in (and so forth D$60. . . ) while column I is the date and time of entry. Column N has the ratings (1-5) which column unfortunately also contains blanks as well.

                any help would be appreciated.

                thanks
                Last edited by AAE; November 30th, 2010, 01:19. Reason: delete quote

                Comment


                • #9


                  Re: If Cell Date a Certain Month Then....

                  Hello syecon,

                  Welcome to Ozgrid.

                  Posting your question in threads started by others is a violation of the Forum Rules and is known as thread hijacking. Posting solutions is acceptable.

                  ALWAYS start a new thread for your questions. You may, if you find it helpful, provide a link back to this (or any other) thread.

                  Start a new thread and be sure to give it a title using only search friendly key words that accurately describe your thread content or overall objective and avoid the use of non-essential words such as "I need", "Help", "urgent", "desperate", etc.
                  AAE
                  ----------------------------------------------------

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

                  Comment

                  Working...
                  X