Google Sheets: What am I doing wrong?

May 29, 2024 12:53 PM

How do I get the spreadsheet to automatically update a range of values? This seems simple, but it has me stumped and my google-fu is failing.

Please see this screenshot.

• Page 1 has the overview.
• On Page 2, you'll see I have two columns (B and D). I have formulae that summarize both columns in B22 and D22.
• In Page 3, you'll see that I inserted a new row at the top (B2 and D2). But the summarize formula didn't update themselves to incorporate the new row.

How do I nudge the summary cells in row 23 to automagically (dynamically?) update themselves every time I insert new data. What you don't see in these screenshots is that I have something like 10 other columns to summarize.

Updating all of these formulae manually by hand makes me nervous. I'm not always the most accurate typist.

Help?

posted by zooropa to Computers & Internet (6 answers total)

Best answer: You can just do your calculations with B:B and D:D -- you don't need the row numbers unless you're explicitly only trying to calculate a subset. Structure your tables so there's not any extra info in either B or D (the headers should be ignored if they're not calculateable) and then it should work for you.

If you really need to do the subset of rows, have your calculations be B1: B21, where B1 is the header or a blank row below the header and B21 is always a blank row -- then you can insert a new row between those markers, which will automatically expand your selection.
posted by AzraelBrown at 1:02 PM on May 29 [2 favorites]

I'd probably do a pivot table here instead of a SUM() formula, but echoing Azrael -- Put your summary into another tab of the sheet and have it summing across B:B/D:D instead of specific rows.

(But yeah do it as a pivot table instead of making sum fields)
posted by brainmouse at 1:04 PM on May 29

If for whatever reason you can't restructure your table so the data columns don't contain any summary cells, then change the formula in your summary cell to =SUM(OFFSET(D$1,1,0):D19) (and adapt this with COUNTA for column B).

This range will always start at the cell one row below your header. But the reference to D19 will be incremented (to D20 etc) when you add a new row at the top. So the summary cell will update to sum the whole range.
posted by Klipspringer at 1:10 PM on May 29

Response by poster: Thanks everyone. Inserting blank rows before and after the range proved to be the easiest and quickest solution.

Thanks, y'all. The Green comes through again! :)
posted by zooropa at 1:50 PM on May 29 [1 favorite]

Response by poster: Now if only Uber and Lyft would make there data exportable and more easily manipulatable. I'm having to retype this data manually and it's a PITA.

Yes, you can download summaries from both platforms, but much of it is in PDF format. Why both Uber and Lyft haven't made an API available for some gung-ho developer(s) to create apps (mobile or app) that would enable someone to export all their data as a CSV or XLS and import it into a spreadsheet is a mystery to me. It's 2024, right?s
posted by zooropa at 1:55 PM on May 29

FYI- you can import data from PDF to Excel.
posted by lucasks at 6:23 PM on May 29