Make sure to first delete excess rows before using arrayformulas... for example if you have 100 rows of data make sure you don't have more than 200 rows in your speadsheet... (highlight right click and delete excess rows) The following formula will do what you are asking for assuming row 1 is headers.
Paste the following in cell F1
You want to build a query where certain criteria are triggered by selection of checkboxes. You have already set up formula to create dynamic lists of the checked criteria.
The data consists of:
the primary table (4 columns x 15 rows)
Criteria 1: Type - 3 x checkboxes, and 1 x dynamic list
Criteria 2: Team - 3 x checkboxes, and 1 x dynamic list
Criteria 3: ...
onEdit / onChange triggers aren't triggered by formula result changes, they are only triggered when a user edits the spreadsheet by using the Google Sheets UI.
The solution is instead of monitoring for formula results changes look for the cells being edited by the user whom will make the formula results change.
You need to wrap your sumifs in a sum so like so (due to you using the array trick):
Start Date Number of Days
Assuming 3/1/2019 is cell A2 use the following formula =arrayformula(sequence(B2+1,1,0)+A2)
and the result will be:
If you want to do weeks a simple modification like this will give you the exact result you asked for:
The formula that would give you the desired results would be:
It is a bit convoluted but works fine.
(following OP's comment)
To exclude empty cells (which are counted as zeros altering the result) we adjust the formula by adding the FILTER function.
You can use this simple formula:
try this. It's cumbersome but it works
You can also use this single robust formula in cell B2:
REGEXREPLACE(TRANSPOSE(SPLIT(TRANSPOSE(ArrayFormula(join("♣︎", A2:A))),"♣︎")),"(?:.?(@[A-Z][a-z]\b))|(.*)","$1"&" ")," ")
An even simpler formula can be used if you don't have any empty rows in ...
You want to display the relevant year that matches a Day/Month value in Column B that is understood to be sorted in descending order.
The format of the Day/Month values are not consistent. Some values are dates while others are strings. This is confirmed by isdate().
There are two formulas:
Cell A2, copy to bottom of page: ...