How to SUM all cells in a column that are equal zero in the same row but previous column? (Google Sheets)

0
votes

This is my first question on StackOverflow.

I am trying to SUM all cells in a column that are equal to zero in the previous column in Google Sheets.

Example Sheet in Google Sheets

I can easily SUM each column or the differences between them. However, I also need to SUM non-zero cells that are zero in the previous column.

For example: I need to SUM cells C4 and C16 as B4 and B16 are 0, but C4 and C16 are not. I need to come up with a formula that will do this for every column starting with Column B.

The result of this formula for Column B = 0 and Column C = 1300 that I would be able to monitor.

Ideally, I also need to SUM cells that are greater (or less) than the cell in the same row but the previous column.

The result for Column D = 250 (D4-C4+D16-C16) but Column E = -250.

I have been struggling with this for days now – I've tried Indexing, matching, but never cracked any of these problems.

Here's an editable Google Sheet. https://docs.google.com/spreadsheets/d/1rA1VMfjsIajVrwoYN7p_uovzze9GZQbsYnqz200LkrI/edit?usp=sharing

2
if-statementgoogle-sheetssumgoogle-sheets-formulaarray-formulas
Whoever solves these, I'd be more than happy to buy you a cup of coffee! - HungiP
i can help. Can you put the "expected" values where you'd like to see them? - MattKing
@MattKing Hey Matt, I added supplemental information and expected values in the shared Google Sheet. Logging off for today, but please let me know if there's anything else I can do to clarify my struggle. Thanks! - HungiP

2 Answers

1
votes

I made a new tab called MK_Data and MK_Summary. I wanted a "clean dataset" so i could design a solution that anticipated future data. On MK_Summary, you'll find some formulas dragged down next to the month-year combos. One is for the Δ $ and looks like this:

=IFERROR(SUM(FILTER(MK_data!$2:$17,MK_data!$1:$1=B2))-SUM(FILTER(MK_data!$2:$17,MK_data!$1:$1=B1)))

The other is for the Δ # and looks like this.

=ARRAYFORMULA(IFERROR(SUM(N(FILTER(MK_data!$2:$17,MK_data!$1:$1=B3)<>FILTER(MK_data!$2:$17,MK_data!$1:$1=B2)))))

Is that what you're going for? were you looking to have separate columns for Value Added and Value Lost? That's easy to change, I just wasn't sure.

0
votes

for starters here is the unpivoted sum logic:

=ARRAYFORMULA({TRANSPOSE(A1:E1), MMULT(TRANSPOSE(ARRAY_CONSTRAIN({SIGN(A2:A17)^0-1, 
 IF(A2:E17<>B2:F17, B2:F17-A2:E17, )*1}, 99^99, COLUMNS(A:E))), ROW(A2:A17)^0)})

0