1

two worksheets countifs for totaling the data

Range("F3").FormulaR1C1 = "=COUNTIF(data!C2,numbers!RC14)"

from C2 i get all of column B in data. perfect. from RC14 i get column N in numbers...but row 9. i need N3.

im sure its probably something stupid...what am i missing?

TideRunner
  • 9
  • 1
  • 5
  • 1
    *Welcome to [so]!* It may be easier to help if you [edit] your question to include an example of your data and what you're trying to do.... but the first thing I would do it copy and paste your formula `=COUNTIF(data!C2,numbers!RC14)` into a cell on the worksheet. Does it works there, as-is? Also, check out the [tour] (you'll earn your first badge!) and well as "[ask]" and [mcve]. Other tips from the site's top user [**here**](https://codeblog.jonskeet.uk/2010/08/29/writing-the-perfect-question/). – ashleedawg Jul 30 '18 at 01:03
  • Are you using A1 or R1C1 notation? Check out the difference between the two on [*this page*](https://bettersolutions.com/excel/formulas/cell-references-a1-r1c1-notation.htm). Are you aware `RC14` as it is, is referring to `Column RC, Row# 14`? Do you have the correct setting picked in Options/ – ashleedawg Jul 30 '18 at 01:10
  • it autocorrected to `=COUNTIF(data!C2,numbers!R[6]C14)`. i have no idea where the [6] is coming from???? yes i have R1C1 checked in the options. i just dont understand why the 6 row offset. – TideRunner Jul 30 '18 at 01:10
  • The `[6]` is part of R1C1 notation. Please see [**this link**](https://bettersolutions.com/excel/formulas/cell-references-a1-r1c1-notation.htm) showing the differences. Is there a reason that you need `R1C1` instead of the standard `A1` notation? A requirement of your assignment, I assume? – ashleedawg Jul 30 '18 at 01:12
  • i know that the [6] is part of the notation. i just dont understand WHERE it is coming from. why did it autocorrect to it. what can cause that offset? yes its part of the assignment – TideRunner Jul 30 '18 at 01:16

1 Answers1

0

RC14 is a reference to that same row that the formula is on but in column 14 (column N). If you are getting N9 then your formula is on the 9th row, aka $N9. The R is relative and C14 is an absolute reference to column N.

R[6]C14 refers to column N but 6 rows past the row the formula is on. If the formula is on row 9 then this is $N15. The row is again relative, the column is absolute.

R[-6]C14 refers to 6 rows above the row the formula is on, e.g. $N3.

R3C14 is a row absolute, column absolute reference to $N$3.

  • the formula is on the 3rd row though. im still getting N9. – TideRunner Jul 30 '18 at 01:22
  • If the formula is on row 3 and you want $N3 use RC14. R[6]C14 gives you $N9 (6 rows below the current row). –  Jul 30 '18 at 01:23
  • im using RC14 on row 3. and getting N9 back. thats the issue. – TideRunner Jul 30 '18 at 01:24
  • I don't see how that is possible. In fact, I've run my own 3 worksheet test and cannot reproduce your issue. –  Jul 30 '18 at 01:30
  • okay...i changed it to `Range("F3").FormulaR1C1 = "=COUNTIF(data!C2,RC14)"`. for whatever reason the `numbers!` did something. i'll take it as a win. – TideRunner Jul 30 '18 at 01:33