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.
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
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:
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.
Last edited by AAE; November 30th, 2010 at 00:19. Reason: delete quote
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)