Return the number of weeks that a specific criteria is met (without double-counting)
I need a method to calculate the number of weeks that specific criteria are met, without accidentally double-counting if that criteria is met twice in the same week, and repeat that process a few hundred times dependent on a key value that I can look up.
The goal here is to essentially repeat, for each client number on my list, the number of unique weeks where any employee worked with them for more than 40 hours. Each client appears only one time on the final list, regardless of the number of employees that have worked with them. I would do this process manually, but I'd need to do it about 750 times and each client has vastly different scheduling. As well, some employees work with multiple distinct clients, and more than one employee may work past 40 hours in a week with the same client, due to a number of unexpected circumstances. Additionally, I'll have to expand on this method eventually to further narrow this down when given additional follow up criteria.
I can't disclose any of the original data. I've instead provided my own mock-up data which I roughly recreated in Google Sheets - the original data is in Excel, hence my choice of venue for this post. In the sample data, I use whole numbers. The real data will include decimals. Since I'm looking for anything over 40 hours anyway, this shouldn't affect the final output.
I've tried to make it easier on myself by creating a table that looks something like this:
The weeks are already specified, and go from week 1 to week 53. The table I'm reporting the values on is currently on a separate page, like so:
In the real data set, these are both tables, so I can make use of table references. I'm also not against using other methods like a pivot table, which I can then just paste into the final report, or more advanced functions if there's just nothing else to handle what we're trying for. I also am in possession of the original time card data that I used to create "Table 1", the hours per employee per client per week.
I've tried to do Xlookup, Countif and Countifs to make this work, but I'm growing quickly concerned that I'll end up having a formula with more lines than my original source data if I go at it that way, and as much as I'd be okay with writing a completely incomprehensible formula, this data will eventually be seen by people outside my organization, and I suspect the receiving organization would prefer to be able to actually open this when they get it. I also tried pivoting out a new pivot table where I return a 1 if the employee worked over 40 hours in a specific week with a calculated field - that just ran into the double counting problem. If I sum up everything for each client number, I'm guaranteed to count the same weeks twice if any two employees worked over 40 hours. At this point, I'm not certain what else is worth spending time on, and I'm quickly running out of functions I actually know how to use.
Any help that gets me closer to a repeatable method is greatly appreciated. Let me know if I'm missing anything that could help make this clearer and I'll add it to the post. Thank you in advance.
[link] [comments]
Want to read more?
Check out the full article on the original site