Active Member
June 8, 2024
Pretty much as the tiltle says:
Effectively counting the number of each day of the week in each month and then total number of guests by Member Type for that day.
If we were too look at May 2024, then the number of Casual Visitors - £20 on a Wednesday would be 3. There were 2 on May 1st and another 1 on May 8th.
The logic seems straightforward, if data within the range is between 1st and 31st May 2024, the dates is a specific day of the week and the Member Type is Casual Visitors - £20, then sum the number of guests.
I need to do this for very day of week, and every member type but if I could get one day, I'm sure I could figure out the rest.
The table is attached.
Thank you
Moderators
January 31, 2022
April 25, 2020
I would do what you want with a Pivot Table.
See attached file. I have changed some or you table headers a bit to allow the pivot table and slicers to be visible on screen along with the data table. I also added another column "Year" which I then hid (that is needed for the Year slicer.)
Active Member
June 8, 2024
I have thought of Pivot Tables and I have some for this data, however I cannot seem to get the Pivot tables to do what I want to do for this workbook.
Perhaps I should have said that within this workbook are currently 81 monthly worksheets. Since October 2017 and each worksheet contains a similar table to the one I posted for each month. This causes two problems: - The data needs to be entered twice, once in the monthly table and once in the master table. The potential for error is obvious and there are months where the monthly one and the master one don't match.
If I use the monthly worksheets, then this formula works perfectly:
=SUMPRODUCT((GuestsJun24[Category]="Casual Visitors - £22.50")*(TEXT(GuestsJun24[Date],"ddd")=T$24)*(GuestsJun24[Guests])) where cell T24 is the day of the week and the table name is changed each month.
If I use the same on the master worksheet, then the result is the total of all 81 months which is why I want to filter the master sheet by the month.
I have uploaded a new workbook with an example of the monthly page.
Moderators
January 31, 2022
Oh my! The last thing I would do is work with separate sheets per month. I'd create on large table, just like in the first file you uploaded. Then load it to the Data Model and use Power Pivot to do all the analysis you need. It may not be possible to make it look exactly like what you have now, but it would be more robust and less prone to error.
The attached file contains an extremely simply example working with a timeline and a slicer, demonstrating only very little of what's possible. Let me know if this is something you would want to consider.
Active Member
June 8, 2024
It wouldn't be my choice either to work like this, but I haven't a lot of options.
The table is for a golf club, and the management really like the way that they can see how things are progressing monthly and on a twelve months and two years ago basis.
Golf is certainly a cyclical business with not a lot of income in cold, wet winter months and then warm summer days with long evenings bring many guests. So it does make sense to compare each month with that of twelve months ago
I have managed to automate almost everything so that making one entry in the monthly table will bring everything up to date. If I could find the formula to replace the one I am currently using and enable me to juts key once into the master table it would be perfect.
Unfortunately, I know absolutely nothing of Power Pivot or the Data Model and the management for whom I have to prepare this wouldn't have any idea of slicers or the like to enable them to view the information presented.
What I have to do is send them a copy of worksheet each month with a dashboard which I construct using various pivot tables from the master worksheet.
I have tried very hard to bring them out of this historical way of presenting things, but they are hard to persuade.
If there is a formula, that would be ideal.
Thank for reading.
Active Member
June 8, 2024
I think this is what I was after:
SUMPRODUCT( (GreenFeeIncome[Member Type]="member playing outside days - £20") * (TEXT(GreenFeeIncome[Date],"ddd")=Z$24) * (GreenFeeIncome[Date]>=DATE(2024,6,1)) * (GreenFeeIncome[Date]<=DATE(2024,6,30)) * (GreenFeeIncome[Guests]) )
It certainly does the trick.
Thanks to everyone who tried to help.
Moderators
January 31, 2022
EDIT: I must have overlooked post number 9 before I wrote my response below. So, perhaps not everything I mention is still relevant.
Fair enough! So I take it that Sheet1 contains the historical Master data and the smaller table in columns AD:AH on Sheet2 is the current month's data entry area used in "the summary" in O24:Z24 on Sheet2.
What I would do is skip the data entry into AD:AH and enter the daily records directly into the Master and then link "the summary" directly to the Master data to avoid entering data twice. You can always hide the historic data and some of the columns that are not relevant for the one that does the data entry.
Enter the start date for the current month somewhere so that you can extend your SUMPRODUCT with arguments that select the correct date range from the Master. Thus, (Greenfeincome[Date]>=start of month)*(Greenfeincome[Date]<=end of month). You can build similar formulas to extract historic income numbers relative to the month you are looking at now, avoiding linking to old reports by hard-coding their names in formulas.
Note that I wrote the formulas in the Summary with partially absolute table references like:
TableName[[column1]:[column1]]
That will fix the column1 and allows you to drag cells with structured table references across.
Finally, I noticed that there were some inconsistent category names for the member types in the Master. Some were named "Member ........" rather than "Members ........". I corrected these in the Master. I also added the data after June 5. All changes are marked in yellow.
1 Guest(s)