Forum Discussion

Jon Cohen's avatar
Jon Cohen
Copper Contributor
May 13, 2018

Define a range based on the data in a spreadsheet

I have a two part question.  Background:  twice a year, I have to process a large spreadsheet of raw data -- it could have 40 columns and over 50,000 or so rows.  Each time, the number of rows will be different.  I would like to do two things:

 

1)  I would like to create a formula so that I can put a count of rows in a designated cell.  Note:  there are some columns that will have data in every cell, up until the end (the cell after the last cell will be blank).

 

2)  Given that number (now stored in a cell), I'd like to use that number to define a range in some formulae.  For example, to add the numbers in column C, I'd like to say something like "=SUM(C1:Cnnn)", where "nnn" is the number of the last row, as determined by Question #1, above.

 

There's probably an easy way to do this.  Any hints?

excel
Formulas and Functions

4 Replies

    • Jon Cohen's avatar
      Jon Cohen
      Copper Contributor
      May 14, 2018

      I'm afraid I need a little more help.  I get that CNTL-T defines & formats a table.  I've been doing that.  But your link to "structured references" doesn't work for me.  I did find some info in Excel's Help function, but I'm still confused.

       

      Here's the formula I am using:  "=SUMIF($AP$2:$AP54513,"TRUE",$AI$2:$AI54513)" where the values in column AP are either "TRUE" or "FALSE", and if it is "TRUE", I add the value in column AI to the total.  The end value of the table, 54513, is hard coded.  The values in Row 1 are headers.  So, if column AP has the header "GoAdd" and column AI's header is "Sales", how do I use structured references?

       

      I can see how I can start entering a simple formula in a cell and then just click on another cell to get a structured reference to the clicked-upon cell, but how do I use a structured reference to a column, and specifically, how do I replace the SUMIF formula listed above?

       

      Finally, how can I capture the number of rows in a table?

       

      Thank you for your help.

Resources