Tina has attended:
Excel Advanced course
Countifs with a date range
I am trying to create reports from server logs.
I have stas on departmental use of tools
For example: in september students from anthropology hit the student help and resources area 600 times, october 200 and november 70
I have a range studentDepartment
and another range dates
Each hit creates a date entry in the spreadsheet.
I want to write a formula that will show all of the hits in a specific month range, so i tried this:
=COUNTIFS(hitByDepartment,A2,DateRangeHits,"<=01/09/15 ")
=COUNTIFS(hitByDepartment,A2,DateRangeHits,"<=01/10/15 ")
This outputs a cumulative score. I don't want that, I want the actual hits.
How do i do this??
Thanks
RE: Countifs with a date range
Hi Tina,
If you don't want the cumulative score then you may need to nest this, to make it sequential, so it checks between. I'm sure you covered Nested IFs on your course checking between criteria. You would have to put each date range into the Formula, which would get longer with time.
Have you considered analysing the data with a pivot table? This would provide you with the count by department, you can then group the dates into months. This would give you actual hits/month and a separate total.
If would like to continue with the formula, please let me know. If not, then I can explain how to setup the pivot table.
Kind regards
Richard Bailey
Microsoft Certified Trainer
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Will be marked as resolved in 5 days
Notice: This is an automated message. Due to inactivity, this forum post will be marked as 'resolved' if there are no further responses in the next 5 days.
