Hello,
I'm having troubles figuring out the proper process to count the number of trades that are off on a certain date, using the following tables.
There is a table for the Personnel, linked to their trade (A1:D13), which limits their trade selection to a table of trades (Trade List).
There is a table for the days off a person has scheduled, where their days off are listed as a variety of symbols at the intersection of their name and date - currently only D is listed for Days Off (A15:P...). This will be the primary entry table that will be updated throughout the year.
Finally, there is the output table, that lists the trade against a date, and shows how many of each trade are off that date (F1:L4).
This output table would ideally remain a table to allow filtering by the date, but any other table can be switched to a range if it helps facilitate the process. I image some form of countifs would be required, but I'm admittedly fairly new to working with ranges and arrays for counting, especially when it's referencing multiple.
To summarize, I'm hoping for the final table to count the number of electricians off based on the date in the F column, by looking at the employees with the trade designation in the header, and then searching the days off table for a letter in the intersecting column. We would like to avoid power query or macros if possible. Any advice would be greatly appreciated!
Thanks,
Apologies for not explaining the personnel/trade table. The first two columns represent their two disciplines. They would always have a minimum of one, but may have two. The third table is just a concatenate I did to try and simply the functions by searching for partial matches, but forgot to remove before posting this. So if they are a welder and mechanic, one of each would be listed as off that day.
As for the output, see below pertaining to the example screenshot I gave;
Since Bob (Welder), Sam (Electrician), and Kyle (Mechanic and Electrician) are all off on that day, you have two electricians, one welder, and one mechanic off.
Also, no the range layout doesn't matter, it's just important that the personnel/date table is vertical.
I'm on the latest version of excel and Microsoft 365, and have access to all formulas, I'm just looking to avoid query and macros to simply the process - it's fairly important it's executed using only functions.
Hope this makes sense,
I see, thank you. If change the input a bit it could be like
with formulae as
=LET(
nDates, 3,
nTrades, COUNTA(Trades[Trades]),
dates, SEQUENCE(nDates,,DATE(2023,1,1) ),
header, TOROW(Trades[Trades],3),
offDays, LAMBDA(date,trade,
LET(
offNames, FILTER(DaysOff[[Person]:[Person]],
DaysOff[[Date]:[Date]]=date),
tradeNames, FILTER(PeopleTrades[[Personnel]:[Personnel]],
PeopleTrades[[Trade]:[Trade]]=trade),
IFERROR(SUM(--(offNames=TRANSPOSE(tradeNames) ) ), 0)
) ),
fillOff, MAKEARRAY(
nDates,
nTrades,
LAMBDA(n,m,
offDays(
INDEX( dates,n,1),
INDEX( header,1,m) ) )
),
VSTACK(
HSTACK( "Date", header ),
HSTACK( dates, fillOff ) )
)If such input is not desirable when transpose to like form within calculations.
If to build PivotTable we need to add source data to data model first. Better to do that with Power Query, but if source tables do not require any transformations that could be done directly.
Also directly in Power Pivot or by Power Query to add Data (aka Calendar) table which covers dates for all desired periods.
Relationships could be set as
Measure to use
WasOff:=CALCULATE(
COUNTROWS( DaysOff ),
TREATAS( VALUES(PeopleTrades[Personnel]), DaysOff[Person] )
)Resulting PivotTable looks like
