troubleshooting Question

How can I set up a report to record entries per month for the whole year

Avatar of Dot Glindemann
Dot GlindemannFlag for Australia asked on
Microsoft ExcelMicrosoft Office
7 Comments1 Solution67 ViewsLast Modified:
Hi, I have a spreadsheet that works well but Ihave to set it up for each year by changing the formula from 2017 to 2018.

This is the sample of the formula I am using for my 2017 Rport:

I am using my date column (B) to count how many times something is recorded for each month and I have used rows 7 to 502 as I don't expect to have more than 502 entries for the year.

=COUNTIFS('2017 Register'!$B$7:$B$502,">31 Dec 2016",'2017 Register'!$B$7:$B$502,"<01 Feb 2017")
=COUNTIFS('2017 Register'!$B$7:$B$502,">31 Jan 2017",'2017 Register'!$B$7:$B$502,"<01 Mar 2017")
Up until Dec where I use;
=COUNTIFS('2017 Register'!$B$7:$B$502,">30 Nov 2017",'2017 Register'!$B$7:$B$502,"<01 Jan 2018")

I don't want ot have to do this for each year, I would rather use a drop down menu / box that I can choose the year from and the formula references the year chosen in this cell.

I have attached the spreadsheet that I currently use that does work but I would love something less time consuming.

Thanks in advance. Dot
Rob Henson
Finance Analyst

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros