0

In Google Sheets, I am looking for a formula to use one column as a diff and another as sum. BUT it should ignore rows where the diff is empty.

Example case:

  | A     | B   | C   |
  | ---   | --- | --- |
1 | John  | 10  | 50  |
2 | Peter | 20  | 70  | 
3 | John  |     |     |
4 | Peter | 12  | 82  |
5 | John  | 5   | 55  |

C4 is the total of Peters last sum (from C2) plus the diff (B4)

C5 is the total of Johns last sum (which is from C1, and not from Johns last row (3) which had no diff (B3)) plus the diff (B5).

So, what is a good formula for the C-column, that will result in the described values in C4/C5?

Improve this question
4
  • Welcometo Web Applications. Please show what you tried and add a brief description of your search/research efforts as is suggested in How to Ask.
    – Rubén ♦
    Dec 7, 2020 at 17:50
  • @Rubén I thought I did. What is missing?
    – henit
    Dec 8, 2020 at 6:40
  • 1. Would your names be just John and Peter? 2. Would you manually fill in the diff? 3. If you have just two names, one appearing after the another, I would use row() with others. But if there isn't some easy sequence, it would be bit complicated.
    – Severus Snape
    Dec 8, 2020 at 7:35
  • @SeverusSnape This is a simplified example to show what I need to solve. The real example is much larger. 1. No, many other names, just two to simplify the example. 2. No, it is calculated from other columns (in the real world case) 3. The order could vary, that's why it matters to look for the last row with the same name, prior to the active row, as described in the example cases.
    – henit
    Dec 10, 2020 at 7:02

1 Answer 1

Reset to default
1

You want a formula that will maintain a running total for a given person in any line of that person's data.

Before describing the formula, it needs to be recognised that opening/brought forward totals for each person need to be added to the list of transactions. This will create a complete list of transactions for each person.

Line one: John | 40 | 40

Line two: Peter | 50 |50

Formula =sumif($A$1:A1,A1,$B$1:B1)

  • Insert this in cell C1
  • Copy down as many rows required.

It is likely that an arrayformula-based solution can be derived, but this answer will provide an effective, though inefficient solution.

Improve this answer

Your Answer

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

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