68

I want to calculate the product of two different columns (e.g., A & B) for each row on my spreadsheet. For a single row, this would work by using =PRODUCT(A1:B1), but I can't figure out how to get the function to use the current row instead of a manually entered row. I know you can get a reference to the current row using ROW(), but I get an error when I try something like PRODUCT(AROW():BROW()).

whoadave
  • 758
  • 1
  • 5
  • 6
  • This question is probably not within the scope of this forum, but I will go ahead and give an answer anyway. – AdamL Jul 11 '13 at 13:20

2 Answers2

78

If you copy the formula you quoted into another row, or fill it down using Ctrl+D, it will automatically change the reference to the row you are copying/filling to. This is called a "relative reference". If you use the formula =PRODUCT($A$1:$B$1), this is called an "absolute reference", which will not change if copied elsewhere.

You can also use the following formula in any row:

=A:A*B:B

which will return the product of the two values in the row the formula is invoked.

AdamL
  • 23,691
  • 6
  • 68
  • 59
  • sorry, a reference really is substituted automatically, I just need to copy it in a cell which is not in the edit mode (that is a mode which it enters after double click) – d.k Feb 18 '14 at 19:41
  • 2
    This actually refers to the first cell in a given column, not the cell of the current row. – Dissident Rage Jan 28 '15 at 07:03
  • 1
    @DissidentRage, what do you mean by "this"? – AdamL Jan 28 '15 at 23:03
  • Referring to this formula: `=A:A*B:B` – Dissident Rage Jan 29 '15 at 06:59
  • 1
    In that case, I can't replicate what you're saying. `=A:A*B:B` in C5 will return A5*B5. – AdamL Jan 29 '15 at 07:14
  • remember to specifically use `A:A`, not just `A` – Rogue Jan 25 '16 at 21:22
  • 1
    This doesn't answer his question. He asked how to reference the current row. Not how to multiply two columns together. Multiplying two columns together was the end goal. – Alex Apr 27 '17 at 19:53
  • 3
    @Alex the =A:A*B:B formula does not "multiply two columns together". It multiples two cells together; the two cells in the same row that the formulas is placed. A simpler example without multiplication is =A:A. When that is placed in B6, it will return A6. When placed in B7, it will return A7. It will always reference the current row. So I don't understand how I am not answering the question (other than not being able to communicate the answer very well!). – AdamL Apr 28 '17 at 20:32
  • @AdamL Oh I was confused about what your answer did. Thanks – Alex Apr 29 '17 at 18:13
  • @AdamL I am calculating the sums of the cells across a few columns, and tried to replace =SUM(B9:E9)/3 with =SUM(B:B:E:E)/3 but I get error #NAME? – likejudo Feb 28 '19 at 17:14
  • 1
    @likejudo Yes, that structure won't work in the case of referencing multiple columns. A workaround: `=SUM(INDEX(B:E,ROW(),0))/3` – AdamL Aug 11 '19 at 23:18
  • Apparently there are still no answers to the question. It's probably either some reserved word in the spirit of `self` in other script/programming languages or it can't be done (possibly due to looping references or something). This would save us from 2px-wide helper columns so much. – Chico Carvalho Mar 23 '21 at 22:50
  • This answer actually does not answer the question, it only solves his specific problem with a different solution. The question was applicable to many things; in my case, getting a specific column but current row from a different spreadsheet via IMPORTRANGE. – Blue Dev Feb 17 '22 at 13:18
68

One way to do this is using the =INDIRECT function. There are then a couple ways to approach this (plus a bonus, below):

=INDIRECT, A1B1 notation

Option 1 uses the =ROW() function (and =CONCAT to join column and row names) as you were mentioning, and in this case might look like:

=INDIRECT(CONCAT("A", ROW())) * INDIRECT(CONCAT("B", ROW()))

(Note: I'm using x * y instead of =PRODUCT(x, y) because it seems more readable within the answer, given only two factors.)

If you did this on row 3, it would effectively come up with A3 * B3 as the formula.

=INDIRECT, R1C1 notation

Option 2 with =INDIRECT is to use a "relative" reference, using what's called "R1C1 notation". This notation is accessed by passing FALSE to the (optional) second parameter of =INDIRECT, and has two basic forms:

  1. Referring to another cell by it's absolute position, e.g. R1C1 always refers to A1 (first row, first column), R3C2 always refers to B3 (third row, second column – note that the order of row and column is reversed from the B3 notation). These hold true wherever the references appear on the sheet (they'll always resolve to A1 and B3, respectively).
  2. A position relative to the current cell, using R[1]C[1] for example, which indicates 1 row down, 1 row to the right. Basically, this is the same notation, except that you put the numbers in brackets to reference the number of rows and columns away from the current cell (going down and to the right for positive numbers) a particular cell is. While R[1]C[1] refers to 1 row down, 1 column to the right, R[0]C[-1] would refer to the current row (0 is no change), 1 column to the left (negative numbers go up for row and to the left for column instead of instead of down and right, respectively).

These two forms can be combined – absolute for row, relative for column, or vice versa. So if you wanted to multiply the value on the current row in column A to the value on the current row in column B, you could use:

=INDIRECT("R[0]C1", FALSE) * INDIRECT("R[0]C2", FALSE)

This would always give you what you could think of (but not express) as =AROW() * BROW().

(Side-note: I found this description of using =INDIRECT to be helpful in coming up with the above.

$A$1 – anchored references (bonus)

Finally, it's probably worth noting that it's possible to reference fixed columns and/or rows using the $ modifier. Normally, when copy/pasting formulas into other cells, the column and row references change based on the amount of movement: copying a reference to =A1 from C2 to E3 would get you =C2 (A became C because the reference moved two columns to the right; 1 became 2 because it moved down 1 row). The $ notation anchors one or both of these references.

E.g. if you had a reference in to =$A1 in cell C1, and then copied that into C2, it would become =$A2. This much is unexceptional, because =A1 would have copied to =A2 as well. But if you now copied from C2 to D3 (one row down and one column right), it would become =$A3, still anchored to column A, and still changing the row number. So if from any column on row 63 (arbitrary example) you put the formula =$A63 * $B63, you could then copy/paste that to any other cell on the spreadsheet and it would multiply the values in columns A and B of the row you pasted into. (Which I think is maybe what you were trying to do?)

(Also: Note that you can do the same thing with row references - =A$1 is always row 1, but A can change; =$A$1 will always be A1, no matter where you put the reference to it.)

lindes
  • 9,854
  • 3
  • 33
  • 45
  • 4
    Thank you for this detailed answer @lindes – levengli Jan 25 '18 at 19:50
  • 1
    This is a great answer. I used this in order to use dynamic indirect references on conditional formatting formulas. Very well explained @lindes. – Lucas Franceschi Jan 03 '19 at 13:58
  • 1
    This is so ugly. How do spreadsheets not have a cleaner solution to what I imagine is a very common requirement? – mowwwalker Jul 31 '19 at 23:43
  • 1
    @mowwwalker: I think for most common requirements, the `$` notation will get most folks what they want, and that's pretty terse and, once one is familiar with it, easy to grok. And `=INDIRECT(...)` gives full flexibility to do whatever one wants. Seems _pretty_ clean to me. If you've got a better idea, you could always suggest it to someone at Google or Microsoft. – lindes Aug 02 '19 at 18:47
  • Great, small misstake on CONCAT syntax : CONCAT("A", ROW()) --> CONCAT("A"; ROW()) It is ; (not ,) – seb54000 Oct 14 '19 at 11:22
  • @seb54000: Really? I don't know why it should be a semicolon. Indeed, I just opened up a spreadsheet and tried doing a `=concat("a"; row())`, and it converted the semi-colon to a comma. So, semicolon is allowable, but a comma is not a mistake... unless... am I missing something?? – lindes Nov 01 '19 at 01:28
  • I put the comment because it was not working for me with a comma. Can’t remember if I was doing it from iPhone app or from browser workstation, if there may be a link. – seb54000 Nov 02 '19 at 08:21
  • Excellent Answer!!!! I Have searched everywhere for this kind of answer! In my opinion, it should be the top answer; people should always explain things like this. Answer like these keep in mind that newcomers will be reading it for years to come. It really helps save thousands of people thousands of hours of frustration! So... Thank you! – osirisgothra Oct 20 '21 at 12:12
  • @osirisgothra: Thanks... Well, if people keep upvoting it, without upvoting the accepted answer, it may eventually take top place. And hey, they've got "for years to come" time to do so, so... it may happen. :) In the mean time, hopefully others who need this information will scroll enough to find it. And whenever I'm writing an answer on this (or other SE) site(s), I'll keep aiming for this level of utility and clarity of explanation. Anyway, gladly done, and thanks for the kind words. – lindes Nov 09 '21 at 21:26