How to do math in Google Sheets relative to a cell?

I'm working on some things relating to properties of one dimensional cellular automata, and am trying to make a function in which the value of a cell in column A is equal to the sum of all other cells in its row, instead of them all being a specific row. this is to make it so that column A can be a sort of timeline of populations, is this possible, and if so, what statement should I use? For example, if row 1 has twelve live cells, then the value in A1 should be 1+1+1+1+1+1+1+1+1+1+1+1, or 12, but if row 2 has 5 live cells, then the value in A2 should be 1+1+1+1+1, or 5.

• Welcome. Please share some examples with expected results. Edit your post according to How do I ask a good question? . Jan 14, 2021 at 16:25
• Are you already familiar with spreadsheet formulas and Google Sheets built-in functions? Jan 14, 2021 at 18:20
• no I'm pretty new to google sheets. I don't really know much about the built in functions, and so far google hasnt been very helpful Jan 14, 2021 at 19:03

I admit, I don't understand some of what you're saying. But it sounds like you just need COUNTA. For instance, if all you have in each row from Column B to the far right of the spreadsheet is either countable values or blanks, you can count "live" (i.e., filled) cells in B1:1 by placing this in cell A1:

`=COUNTA(B1:1)`

You could then just drag this down as far as needed in Column A by clicking on cell A1, left-clicking and holding down on the tiny square that appears lower right in the blue cell border, dragging the mouse downward and then releasing.

It is also possible to count "live" (i.e., filled) cells in every row using just one formula. But if you are brand new to Sheets, it would likely be beyond what you'd be able to understand quite yet.

Re: your comment that "... so far, Google hasn't been very helpful," Google apps only do what we tell them to do; and that requires learning the tools and logical skills ourselves. There's no fast track to that. By analogy, we can't go out and buy a canvas, paints and brushes and then expect to be an artist; or expect the art store to come and paint something for us.

We each learn essentially the same way: from scratch and piece by piece. Resist discouragement and stick with it. If you do, you'll learn "piece by piece" (just like we all did), until you find yourself down the road somewhere explaining to someone else how to do something that seems "simple" to you (and yet still mysterious to them from where they stand on the road to learning).

• @Rubén, I don't believe you are correct. COUNT counts only numbers. COUNTA counts any/every data type (and whether homogenous or mixed). Jan 15, 2021 at 7:28
• You are right. Sorry. Jan 15, 2021 at 13:35
• @Rubén, if only you knew how many times I still get "simple" things wrong in all this, you'd know there was no need to be sorry (especially for those of us who look at it a ton and often in the middle of the night!). Jan 15, 2021 at 13:45
• this does definitely help, however i was wondering also if there was any way to make a function that can add up cell numbers without having to have a specific row requested. ideally i could do something like "SUM(B[row=self]:[row=self])" and the cells in column A would all automatically display the sum of the values of all cells to the right of it that occupy the same row as the cell doing said check. Jan 15, 2021 at 17:40
• Your end goal is not clear, @zackit. Please set up a sample sheet with realistic data and manually enter the result you are looking for where you want it. If you want a result for multiple rows, include a minimum 3 rows of data. Share the link back here, being sure to set the link's Share permission to "Anyone with the link" and "Editor." That is the most efficient and effective way to help us help you. Jan 16, 2021 at 3:53

Let say that there is a sheet showing this:

A B C D
1 2 1 1
• A1 has `=SUM(B1:1)`. This will return the sum of the values on B1, C1, D1, etc.
• B1 has `1`
• C1 has `1`
• D1 and the cells to the right are blanks

You should start by learning the spreadsheets basics.

1. An spreadsheet has one or more sheets.
2. Each sheet has a grid.
3. Columns are identified by using letters A, B, C, etc.
4. Rows are identified by users numbers, 1, 2, 3 etc.
5. A single cell is identified by using the corresponding column letter and the row number, i.e. `A1`. This is known as A1 notation (there is an alternative notation called R1C1 notation, but I will not expande about it on this answer).
6. Each of the identifiers could be relative or absolute. `\$` is used as a prefix of an identifier to denote that that it's absolute, otherwise it's relative.
7. A continuous set of cells is called a range.
8. A range is identified by combining the reference of the top left cell i.e `A1` and the reference bottom right cell, i.e. `B2`, separated by a colon, i.e. `A1:B2`
9. A cell could store a number, date, time, text or a formula.
10. A formula starts with `=` or `+`
11. A formula could use arithmetic operators i.e. `+` (addition), `-` (subtraction), `*` (multiplication), `/` division, and symbols like `^` to denote exponential
12. A formula could use built-in functions instead operators i.e `ADD`, `SUBSTRACTION`, etc.
13. There are formulas that can use a range as an argument like `SUM` and `COUNT`
14. The syntax of the cell literals and formulas might vary depending on the spreadsheet regional setting, i.e. some countries uses a dot as decimal separator while others use a coma.

NOTE: "live cell" is not an usual term used when talking about spreadsheet. If a cell hasn't any value, usually we say that it's a blank cell or an empty cell, if it has a value sometimes we say that it's non-blank cell sometimes is implicit that the cell we are talking about has a value.

Resources