After searching far and wide I came up with this elegant little solution. It uses INDIRECT to add all columns into one as more columns are added. You then use the indirect cell to combine all columns.
Specify your INDIRECT range in a helper cell first with:
=B6"&":"&SUBSTITUTE(ADDRESS(1, COUNTA($A$2:$CA$2), 4), 1, "") (...
You are using Data validation to make "Room" selections for time periods on a given day. You want to remove existing selections from the range of available "Rooms". The original question has been answered - the solution related to adapting a "helper column" formula to new time periods on a new day.
However, the solution ...
See my comment on your original post.
That said, assuming you want to generate dates for the entire year number listed in A2:
1.) Make sure you have an empty range that can receive 365 dates (e.g., A3:A or B2:B).
2.) Place the following formula in the topmost cell of the above output range:
=SEQUENCE( ("12/31/"&A2) - ("1/1/"&A2) + ...
You can actually get around to using column headers by splitting the Query formula and using other formula's to automatically get the desired column names from a list.
For example if you have a table in range A1:E15 with headers "H1, H2, H3, H4, H5", and you'd like to only get columns H3 & H5:
Store the desired headers (H3 & H5) in another ...
The image answer got me thinking and this seems to work best.
Insert a checkbox in each cell you want them located
Use your arrow key to move to the box you want to be linked
You will notice the formula box shows FALSE if unchecked
Type the following into the cell, A1 representing the controlling box location
To judge which approach might be best for you would be a most difficult thing to do. OTOH with '000's of rows, you may find that some options are more efficient (less effect on recalculation) than others.
Please consider that there maybe/are many valid approaches. This answer covers only two options:
COUNTIFS because you considered this a possibility
For similar situations, I have used filter function with the average and standard deviation.
The filter function selects values in col C corresponding to the criteria in col B, on which the Average and Standard Deviation are calculated.
You can do this with the INDIRECT function, which takes a cell reference as a string. INDIRECT("A1") (note the quotes) is equal to A1. The benefit of this is that you can use & to concatenate multiple strings to create the final reference.
Using this, your example would look like this:
This answer is an adaptation of an answer by @Calculuswhiz to Shuffle comma-separated list of values in one cell (Google Sheets) on StackOverflow.
There is one key difference between the scenario of this question and the StackOverflow question. In StackOverflow, the values were already in a comma separated list. In this question, the values are in a list ...
Got the answer in StackOverflow:
It's enough to wrap the Query with SORT() function
Of course, in the simple example above, use instead =Sort(A1:A4)...