New answers tagged google-sheets-arrayformula
0
Since you want to return the sum of each of 3 columns you could use the following query formula instead
=QUERY(QUERY(A:D,
"select sum(A), sum(B), sum(C) where month(D)+1=2 ",1),
"select * OFFSET 1 ",0)
The above formula will give you results for just the month of February.
You can alter the formula to get results for each month
...
1
You'll first need extra columns converting the dates to months and years. This is as simple as =MONTH(B2) and =YEAR(B2), applied to all rows.
Then you'll need a so-called pivot table to group the data. From the menu, choose Data → Pivot table; select the existing columns and where you want the table:
Add rows, columns and values as follows:
For month ...
0
So, I was pointed in the right direction by marikamitsos, thank you!
However, I found a slightly different solution.
=QUERY($C$3:$D$53,"select D where C='TER 2'")
This article helped a lot > Google Sheets Query
5
It is as simple as using the following sequence formula
=SEQUENCE(A1)
Or you even could have something like this:
=SEQUENCE(A2,1,A4,A6)
You could even use the SEQUENCE function for dates
=SEQUENCE(A2,1,A4,A6)
SEQUENCE
0
Try a query instead. In cell F4 try the following formula:
=IFERROR(QUERY($C$3:$D$52,"select D where C='"&G$3&"'"),"")
You can even drag the formula from F3 to the right and all of the rest will auto-fill.
Functions used:
QUERY
0
Instead of 249 static formulas you can use just 3 dynamic ones.
For your 1st set use the following on cell A3
=QUERY(ArrayFormula(((IFERROR(
VLOOKUP(SORT('solution Stats Gathering'!K3:K,1,0),
({'solution Stats Gathering'!K3:K,'solution Stats Gathering'!A3:L}),{2,3,12},0)
)))),"select * where Col1 is not null")
For the ...
0
You can also do same using DATE function, this gives you ability to generate months or years.
Pretending start date is written in A2 and end date is written in B2.
This formula will generate a list of dates between (inluding start, excluding end).
=ARRAYFORMULA(DATE(YEAR(A2), MONTH(A2), DAY(A2) + SEQUENCE(DATEDIF(A2,B2,"D"), 1, 0, 1)))
Few explanations:
...
0
=SORT(A2:A,ARRAYFORMULA(MATCH(A2:A,C2:C,0)),1)
0
You must have figured it out by now. (good work).
Still. After having a look at your spreadsheet, I would suggest you change your formula in column X to
=ArrayFormula(IF($W2:W="","",COUNTIF('Feed from Register'!$E$2:$E,"*"&W2:$W&"*")))
The change will cut away the 0s going all the way down to the last row and will improve both the readability ...
Top 50 recent answers are included
