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.

enter image description here

1 Answer 1


You can use do it in several steps :

  • check if condition is met
  • set value accordingly
  • sum all

Using =IF (logical_test, [value_if_true], [value_if_false])

enter image description here

=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).

  • The question is ambiguous. Though I felt, OP has some numbers is those cells and OP wants the summation of those numbers, only if the cell (which contains the number) lies below a cell (which contains the string). Feb 10, 2021 at 17:28
  • 1
    I've edited the question to clarify.
    – Ben W
    Feb 10, 2021 at 21:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.