+ Reply to Thread
Results 1 to 2 of 2

How to create expanding formula ranges in table ??

  1. #1
    Registered User
    Join Date
    02-15-2019
    Location
    nhungthientai
    MS-Off Ver
    nhungthientai
    Posts
    1

    How to create expanding formula ranges in table ??

    So basically I want to create an expanding range in a formula in a formatted table.

    So if it was just a normal range the formula would look something like this;

    =COUNTIF($B2:E2,">0")

    Then as I drag the formula along the row it would automatically change as below;

    =COUNTIF($B2:F2,">0")
    =COUNTIF($B2:G2,">0")
    =COUNTIF($B2:H2,">0")
    and so on.

    When I try and do this in a formatted table it either stays completely static, or the whole range moves.

    I did think it may look something like this, but have not had any consistent joy with it;

    =COUNTIF(table_name[[ColumnB Name]:[ColumnB Name]]:[ColumnE Name],">0")

    Any guidance would be gratefully received.

    I am using Excel on Mac if that has any bearing on the solution.

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    6,967

    Re: How to create expanding formula ranges in table ??

    Normally I am a huge fan of Excel tables, but sometimes they can be too helpful. This is one of those cases. Instead of selecting the cells and letting intellisense fill in the cell name, type the formula in manually. The table will "respect" the sheet reference as opposed to filling in the table cell name later. So if you type: COUNTIF($B2:F2,">0") and drag it across you will get the results you want.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1