Forum Discussion
Convert Array formula
This was a bit of a battle, but I converted the output to a table so that the single-cell array formula propagates down the column. For the first row, I introduced a single character wildcard that hoped would match the line-feed.
The formula reads
= LET(
Club, AppInput[Choose Club],
Date, AppInput[Completion time],
Field, XLOOKUP(@category, AppInput[#Headers], AppInput),
chr, IF(@category=AppInput[[#Headers],[Outside of club, entrance, grounds/parking]],"?",""),
COUNTIFS(
Field, Setting&chr,
Club, selectedClub,
Date, ">="&RequiredDate,
Date, "<"&RequiredDate+1) )[I should have included it above but editing a post tends to generate a mess on this forum]
- Thanks, but I cannot follow your formula. Did you use cell/range references?
No, I haven't used direct cell references since 2015. Despite that the formula is a perfectly ordinary Excel formula, albeit one that uses Excel 365 formulas.
The names Club and Date are local to the LET function and give me a more concise method of referring to fields within your AppInput table.
The XLOOKUP looks for the 'category', specified by the current record of the output table, within the Headers of the AppInput table and returns the relevant column from the table.
I then want to use COUNTIFS to return the number of Yeses or Noes but, first, I check whether it is the first column because I know there will be line-feed at the end of each value. The datetime must lie between the requested date and the following date.
I realise my formulas seem alien to someone brought up on the spreadsheet paradigm of cell references, but having described the $A$1 notation as an abomination that has no place is any aspect of technical computing, I could hardly go back, could I?
