google sheets
I'd like to sum the cells in a column where the cell above contains a string. For example, sum all the cells where the cell directly above contains the string "Transport".
For example, in the following (which I have just inserted the correct numbers into), the 'Total transport' cell contains 42 as it is the sum of 10 + 20 + 12, from those cells with the string 'Transport' above them.
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) ''"))
Yes, this can be done.
Following I will show an approach that use the running total formula posted by AdamL but first, it prepares the input data to be used by the referred formula:
=SORT({{A3:B7},ROW(A3:A7)},3,FALSE) =ArrayFormula(SUMIF(ROW(C3:C7),"<="&ROW(C3:C7),C3:C7)) =ArrayFormula(SUMIF(ROW(D3:D7),"<="&ROW(D3:D7),D3:D7)) =VLOOKUP(A1,F3:G7,2) Following there is another approach. I made a slight change to the AdamL's formula (replace <= by >):
=ArrayFormula(SUMIF(ROW(A3:A7),">"&ROW(A3:A7),A3:A7)) =ArrayFormula(SUMIF(ROW(B3:B7),">"&ROW(B3:B7),B3:B7)) =VLOOKUP(A1,A3:B7,2,FALSE)
Best Answer
You can use do it in several steps :
Using
=IF (logical_test, [value_if_true], [value_if_false])=IF(A1="hello", A2,0)- this will set the values in the B column according the existence of a string. Than you can use=SUM(B2:B12).