Forum Discussion
Counting by referencing multiple tables in a single function.
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, linke...
Could you please clarify a bit
Personnel Trades. For example Bob has Welder as Trade 1 and same Welder as Trade 3. Why so ? And, if some reason, Bob is "doubled Welder" and he is off on Jan 1st, does that mean we have TWO Welders off for that date.
Output. That will be much better of in your sample you add as well manually calculated result. Now it is empty in sample.
Input. How critical is such layout of ranges, they are not optimal. If for some reason (which one) we can't use unpivoted layout most probably we shall be do that in formulae and/or helper ranges.
If you'd like to avoid data modelling and PivotTables, which kind of formulas are available in your environment, are you on Excel 365?
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,