1

I have two worksheets, Sheet1 and Data. The total number of rows in Data is always changing depending on a database.

Now in Sheet1 I wanted to calculate the SUM of a column in Data

I understand that =SUM('Data'!R2:R100) will give the SUM of cells from R2 to R100, but in Data does not always contain 100 rows, it can be more or less.

So how can I enhance the formula so that it can always SUM until the last row of Data? Thanks for any advice in advance!

wesley
  • 103
  • 2
  • 14
  • Maybe this answer will help you getting through: https://stackoverflow.com/questions/38882321/excel-vba-better-way-to-find-last-row – Essigwurst Mar 22 '18 at 06:54
  • Thanks you, but is it possible to do it using a cell formula? – wesley Mar 22 '18 at 07:01
  • Just go for maximum row, maybe 100000?. I mean set the row number to the maximum value it can go. `SUM` function will skip the blank cells and give sum of values from non-blank cells. – Rehban Khatri Mar 22 '18 at 07:05
  • I thought of this too @RehbanKhatri, but the data will be increasing from time to time. – wesley Mar 22 '18 at 08:54

1 Answers1

1

Assuming your data is all together, you can use COUNTA() to count the number of non-empty cells in any column. You can then infer (again, assuming you have no random blank rows) the total number of rows.

=COUNTA(Data!R:R)

But from what I understand, you just want to sum the entire column. Therefore, you don't even need to worry about the rows, just use:

=SUM(Data!R:R)

This will sum up the entire column R.

K.Dᴀᴠɪs
  • 9,945
  • 11
  • 33
  • 43