CODEWITHSUNDEEP
google-sheetssumformulas
Beren
Beren

Reputation: 3

Google Sheets: Create formula that auto-updates for new entries & sums cells in other columns

I have a Google Form which feeds values into a Google Sheet. As part of my analysis, I need to SUM() together, in a new Column Q, entries from columns H, I, J, K, and L. I do not want totals of these columns, but Q1= H1+I1+J1+K1+L1, then Q2= H2+I2+J2+K2+L2, etc.

The sticking point is that I also want this formula to automatically populate Column Q as new data is added to H, I, J, K, and L. Is there a solution for this? Can I do this with an array formula?

Upvotes: 0

Views: 4356

Answers (1)

Karl_S
Karl_S

Reputation: 3564

You can use ARRAYFORMULA to do this. You cannot use a SUM() inside the ARRAYFORMULA, but you can use + such as H1:H + I1:I. So testing to make sure H has data and then using that to add your columns, you would get:

=ARRAYFORMULA(IF(ISBLANK(H1:H),,H1:H + I1:I + J1:J + K1:K + L1:L))

The IF() allows us to apply this only to rows with data in column H. We determine if H is blank and if it is, we return nothing. Note that this is different than putting "" in for the TRUE option as "" becomes a value in the cell.

Reading the Formula

This basically reads as "For each row in the spreadsheet, if cell H is blank, do nothing. If cell H1 is not blank, return the result of adding cells H, I, J, K and L from this same row."

Upvotes: 1

Related Questions