Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 9 of 9

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

  1. #1
    Join Date
    24th August 2007
    Posts
    3

    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!

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    7th December 2005
    Location
    Hampshire, England
    Posts
    4,898

    Re: If Formulas With Dates In The Criteria

    Try:

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

  3. #3
    Join Date
    19th January 2005
    Location
    Michigan, USA
    Posts
    844

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,705

    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) 


  5. #5
    Join Date
    24th August 2007
    Posts
    3

    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

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    14th July 2007
    Posts
    759

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

    Dave's formula works for me.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    24th August 2007
    Posts
    3

    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!

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    29th November 2010
    Posts
    4

    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 at 00:19. Reason: delete quote

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    19th January 2007
    Location
    South Carolina, USA
    Posts
    9,206

    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

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Show Month Of Date Added In Cell
    By kitvel in forum EXCEL HELP
    Replies: 3
    Last Post: March 20th, 2008, 17:24
  2. Place Month Of Date Cell On Another Worksheet
    By Ybbob in forum EXCEL HELP
    Replies: 3
    Last Post: February 6th, 2008, 09:28
  3. Increment Month in Date & Use Cell Number For Day
    By jigar987 in forum EXCEL HELP
    Replies: 7
    Last Post: May 7th, 2007, 18:15
  4. Month Serial Without Date In Cell
    By stupidhead78 in forum EXCEL HELP
    Replies: 5
    Last Post: January 13th, 2007, 02:03
  5. Date forumlae (get month from cell)
    By Ladybug in forum EXCEL HELP
    Replies: 19
    Last Post: October 6th, 2005, 15:19

Bookmarks

Posting Permissions

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