Forum Discussion
Count the instances of a name based on the date, into a different sheet
Hi TerriLP
If I didn't make mistake replicating your setup that should be:
in B2 then copy right & down (adjust $G$100 and $B$100 according to your actual ranges):
=COUNTIFS( 'FACILITY REQUESTS'!$G$1:$G$100, $A2, 'FACILITY REQUESTS'!$B$1:$B$100, ">=" & B$1, 'FACILITY REQUESTS'!$B$1:$B$100, "<=" & EOMONTH(B$1,0) )NB: Enter real dates (1/1/24, 1/2/24,....1/12/24) in B1:M1 then Custom Format cells to display Month names only
Thank you both for your super fast replies. I did try both ways and both work to a degree.
Lorenzo Your way seemed to work best for me except as far as I can tell I would need a column for every day of the year. Is that right, or is there a way to put a month's worth of dates into one cell?
mtarler I have only used a very simple Pivot table once and it was many years ago so I'm probably missing some important knowledge and a quick internet search wasn't super helpful. As with LZ's method, I don't see a way to display the total for the entire month rather than for each day of the month. In addition, I don't see that the table updates in real time, which is very important. I am not the person who will be gathering the information from the totals sheet so it needs to be ready whenever she needs it.
Again, I thank you both for taking the time and for being so quick about it as well.
you're very welcome.
In both cases you should be getting monthly totals.
In Lz's case the COUNTIFS statement uses 3 conditions, the 1st is based on being the correct name, the second is >= B$1 which is the top of the column and although all you see is the month name it is really the 1st day of that month and then 3rd is <=EOMONTH() which is the end of that month
In the Pivot Table, I noted you should drag the Date to the Columns box which should automatically create multiple items including MONTH. By leaving MONTH in there (and removing Day and Date) you will see the totals by month. Basically what I'm saying is that when I dragged "date" to the Columns box then EXCEL created Days(date) and Months(date) items.
- How interesting. I tried a couple more times to get the results that you said I should, but it just would NOT give me more than one day's results. So I recreated the spreadsheet from scratch and pasted in information from our live workbook.. perfect! Both solutions. I'm not sure what's breaking the original. I did try a lot of different things before asking for help. There's a high chance that I accidently changed the date format on Facility Requests at some point.
Whatever the cause, I'm extremely grateful that you've saved us a ton of time. Thank you, both of you!
