[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
Solved

# sum ifs with between two dates in excel

Posted on 2011-09-21
Medium Priority
200 Views
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
1 Comment

LVL 50

Accepted Solution

barry houdini earned 2000 total points
ID: 36577175
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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
###### Suggested Courses
Course of the Month19 days, 5 hours left to enroll