Dec 01 2023 10:06 AM
Greetings. I am trying to set up a spreadsheet that tracks productivity for staff using Office 365.
I am working with 2 spreadsheets in the workbook. The first sheet is named "FACILITY REQUESTS". The other is named "TOTALS".
In the Facility Requests sheet, Column B is the date we are looking at. The format is 1/1/2024. In that same sheet, Column G will be staff names. One name is "Sue".
In the Totals sheet, Column A will be a list of staff names, Columns B through M headers will be the months of the year, starting with January 2024.
I need to track how many times "Sue" appears in column G between Jan 1 & Jan 31, 2024 in Facility Requests and output that information to B2 on the Totals sheet.
We currently use Data Validation in the names column (G) and would like to keep it that way if it's possible. If not, staff is just going to have to learn how to spell their own names.
Thanks!
Dec 01 2023 10:48 AM - edited Dec 01 2023 10:57 AM
SolutionHi @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
Dec 01 2023 10:49 AM - edited Dec 01 2023 10:51 AM
data validation is a very good thing.
It sounds like a Pivot Table might be exactly what you need.
your rows would be user names
then add date to columns and that should auto create months and other break downs (get rid of the ones you don't want. probably just keep years if included and months)
then add names to Values box also
here is a screenshot example
Dec 01 2023 12:07 PM
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.
Dec 01 2023 12:43 PM - edited Dec 01 2023 12:46 PM
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.
Dec 01 2023 01:26 PM
Dec 02 2023 01:43 AM
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?
I'm afraid I don't understand. Either it's me who don't understand your setup and/or what your process is to "fill" 'FACILITY REQUESTS' or you didn't provide the right picture 🙂 if I may
So, if what appears to be best in your contex (dynamic reporting) is doable with what I suggested, fine (there's something even more dynamic below given you run 365). Otherwise please post a couple of pictures of your actual setup and explain how Sue, Paul... record info. in 'FACILITY REQUESTS' (I suppose) and when (every day, hour...)
365 option - Same as what I shared earlier but no more copy right, down...:
where TotalFacilityRequests is a LAMBDA function defined in Name Manager to which you pass the Year (in C2 above) you want to report on. To experience it enter 2025 in C2 in the attached file and/or add a new name(s) at the bottom of table Employee in sheet 'PARAM'
NB: I used Tables as they offer (amongst other benefits) dynamic ranges. That's not mandatory but recommended 🙂
Dec 02 2023 04:32 AM
Hi@Lorenzo Thank you for replying. It was my spreadsheet that was bugged, not your solution. Once I recreated the spreadsheet from scratch, your original solution worked flawlessly. Thank you! Sorry for the confusion.
Dec 02 2023 07:26 AM
@TerriLP No problem at all. Glad you have it working now
Cheers
Dec 02 2023 11:11 AM
Dec 01 2023 10:48 AM - edited Dec 01 2023 10:57 AM
SolutionHi @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