Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

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

1. I agreed to these rules
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. Super Moderator
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. 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. 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. I agreed to these rules
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. Established Member
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. I agreed to these rules
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. I agreed to these rules
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 01:19. Reason: delete quote

Excel Video Tutorials / Excel Dashboards Reports

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.

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.

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

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