New answers tagged google-sheets-arrayformula
0
EDIT
(following OP's comment
I need exactly all criteria . If disposition is "consultation " AND primary is either "Ob"or"surgical" And consultation contains eithe" IM"or "cardio"....
You can easily alter the formula with something like the following
=ArrayFormula(sum(
N(REGEXMATCH({...
1
I used this and solved as well
=countifs(A1:A,"<>")+countifs(A1:A,"",B1:B,"<>")
2
Please use
=QUERY(INDEX(A1:A&B1:B),"select count(Col1)",0)
1
Try this:
=COUNTA(IFERROR(FILTER(ROW(A:A),A:A&B:B<>"")))
ROW(A:A) gives us "something" to count.
By concatenating each row-cell of A:A with the corresponding row-cell of B:B, we form one string, which we can evaluate as one string and thus count as one string.
1
You can address your issue by using 2 similar formulas as shown in the tab I have created in your spreadsheet.
The most challenging of the two is in cell P4 where we calculate the times
=INDEX(IFERROR(REGEXREPLACE(FLATTEN(
SPLIT(QUERY({C4:C&"@"},"where Col1 is not null",0)
,CHAR(10))),"@"," ...
2
This comes down to all those algebra classes no one thought they'd ever need in real life. I can't teach algebra here, but the basics:
X = base year (from which all other years follow, i.e., your B6 amount)
Let B = your discount. Rather than thinking of it as what is taken away, however, we should think of B as what remains. This leaves us with XB (which, in ...
0
To get results by year and month, try the following in E1
=QUERY(ARRAYFORMULA({TEXT(A1:A,"yyyy-mmm"),B1:C}),
"select Col1, count(Col3)
where Col3=TRUE
group by Col1
label count(Col3) 'Half/month' ",1)
OR
To get results by just month, try the following in G1
=QUERY(ARRAYFORMULA({TEXT(A1:A,...
1
You want a running sum of values in Column D based on a conditional value in each row of Column B.
Your scenario is VERY similar to this Webapps precedent ArrayFormula to compute running average for groups of rows. In that case, the desired result was a running average. I have modified the accepted answer by @AdamL. I have also added an alternative found on ...
1
Given the examples in the post and in the sample spreadsheet (which are all in military time format of hh:mm), I used this in B1:
=ArrayFormula({"Condensed Answers";IF(A2:A="",,TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(IF(ISNUMBER(SEARCH(A2:A,"All Day")),"All Day",A2:A)),&...
2
You need to wrap your formula with an Arrayformula and generate as many rows as column A in Sheet1 has a value in it:
=ARRAYFORMULA(if(len(Sheet1!A2:A),
HYPERLINK("https://docs.google.com/spreadsheets/d/xxxx/edit#gid=xxxx&range=A"&row(A2:A)
, "Sheet1-A" & row(A2:A)),""))
Sheet2:
Sheet1:
Top 50 recent answers are included
