google sheets
Assume I want to calculate the weighted value of "value 2" based on the total of "value" if an indicator = 'Y' , how to do this without adding an extra column?
So essentially I want to know how I can apply an expression for each row in a certain range ("artificial column") which I can then apply e.g. a SUM on.
In the screenshot below, how can I calculate the Red Formula without adding an extra column?
Somehow this seems appropriate.
=ARRAYFORMULA(
QUERY(
{
IFERROR(IF(A2:A = "devops", A2:A, TRIM(REGEXEXTRACT(A2:A, "devops "))),""),
B2:B
},
"SELECT Col1, SUM(Col2) WHERE Col1 <> '' GROUP BY Col1 LABEL SUM(Col2) ''"
)
)
copy / paste
=ARRAYFORMULA(QUERY({IFERROR(IF(A2:A = "devops", A2:A, TRIM(REGEXEXTRACT(A2:A, "devops "))),""), B2:B}, "SELECT Col1, SUM(Col2) WHERE Col1 <> '' GROUP BY Col1 LABEL SUM(Col2) ''"))
This can be done by using indirect in R1C1 notation. The following formula matches your situation:
=indirect("R" & G201 & "C" & column()-6 & ":R" & G201 & "C" & column(), False)
The range is described by a string formed by concatenating
R<row number>C<column number>:R<row number>C<column number>
The row numbers are taken from G201, the column numbers are computed with the column() function that returns the column number of current cell. (By the way, A is 6 places to the left of G, not seven)
Best Answer