Solved

sum ifs with between two dates in excel

Posted on 2011-09-21
1
175 Views
Last Modified: 2012-06-27
I have this formula that im trying to make work, its almost there:

=COUNTIFS( 'Requests RAW'!$O:$O,">="&DATE(2011,1,1), 'Requests RAW'!$O:$O, "<="&DATE(2011,1,31),'Requests RAW'!$L:$L,$J7,'Requests RAW'!$D:$D,$H$4)

The bit that doesnt work is the part where im trying to make sure the month is in feb 2011. It almost works but for some reason it wont count dates if they are on the first or last day of feb so the count is 1 or 2 away from the actual total as if I were listing results in a pivot table. Any ideas?
0
Comment
Question by:cookiejest
1 Comment
 
LVL 50

Accepted Solution

by:
barry houdini earned 500 total points
Comment Utility
Your quoted formula is counting dates in January not February but I assume your query still applies. Do you have times in with the dates in column O? If so a date/time on 31st Jan will actually be greater than DATE(2011,1,31), try changing to this version using < DATE(2011,2,1)

=COUNTIFS( 'Requests RAW'!$O:$O,">="&DATE(2011,1,1), 'Requests RAW'!$O:$O, "<"&DATE(2011,2,1),'Requests RAW'!$L:$L,$J7,'Requests RAW'!$D:$D,$H$4)

regards, barry
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
EXCEL Addin problem 7 21
Filter and delete 6 14
Thanks for the advise, but... 4 24
Excel Macro - Lookup-Highlight 4 24
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now