+ Reply to Thread
Results 1 to 7 of 7

How to count a range relative to a dynamic reference

  1. 04-01-2014, 06:25 PM #1
    xargon666
    xargon666 is offline
    Registered User
    Join Date
    04-01-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    3

    How to count a range relative to a dynamic reference

    Hi

    Bit of an excel noob banging my head against a problem (2010 btw).

    I am trying to create a formula to count a range relative to a dynamic reference (at least I think that's you would phrase it).

    I thought I was on to a winner with this:

    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    but it doesn't seem to accept the : as a legal operator.

    I suspect the answer is to use SUMPRODUCT somehow but I am not to savy with it.

    any advice would be greatly appreciated
    Last edited by xargon666; 04-01-2014 at 06:27 PM.
    Reply With QuoteRegister To Reply
  2. 04-02-2014, 06:25 AM #2
    foundy
    foundy is offline
    Registered User
    Join Date
    03-28-2014
    Location
    Hertford, England
    MS-Off Ver
    Excel 2010,2013
    Posts
    38

    Re: How to count a range relative to a dynamic reference

    Hi,

    Just need to add indirect to take the formula and turn the : into a string ":".

    Please Login or Register  to view this content.
    ADDRESS returns a string, using INDIRECT, uses the string as the location.
    Reply With QuoteRegister To Reply
  3. 04-02-2014, 06:42 AM #3
    XOR LX
    XOR LX is offline
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: How to count a range relative to a dynamic reference

    Hi,

    I belive that this accomplishes the same thing, though without the volatility:

    =COUNTA(INDEX(B:B,MATCH(H2&-1,A:A)):INDEX(E:E,MATCH(H2&-1,A:A)))

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/
    Reply With QuoteRegister To Reply
  4. 04-07-2014, 04:21 PM #4
    xargon666
    xargon666 is offline
    Registered User
    Join Date
    04-01-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: How to count a range relative to a dynamic reference

    Hi

    Thanks for your comments

    I think foundy hit the nail on the head there, and I have tweaked the final product to fit my tables, resulting in this:

    Please Login or Register  to view this content.
    This seems to work until I get to non-existent data, and the match function brings up #N/A, which COUNTA will count.

    I tried to remove the #N/A's by using the IF function. It didn't work, but it sure got ugly:

    Please Login or Register  to view this content.
    I got this far and it produced an error, to nobody's surprise . IS there a more elegant way of not counting errors?

    Cheers
    Reply With QuoteRegister To Reply
  5. 04-07-2014, 04:55 PM #5
    foundy
    foundy is offline
    Registered User
    Join Date
    03-28-2014
    Location
    Hertford, England
    MS-Off Ver
    Excel 2010,2013
    Posts
    38

    Re: How to count a range relative to a dynamic reference

    Hi,

    how about:

    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  6. 04-08-2014, 04:23 PM #6
    xargon666
    xargon666 is offline
    Registered User
    Join Date
    04-01-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: How to count a range relative to a dynamic reference

    Hi

    I'm afraid that didn't work either, but I think I know why... ISERROR seems to be to general. And the IF(ISERROR( fucntion needs to target the part of the formula producing the errors, not the count. I tweaked it around a bit more and came up with...

    Please Login or Register  to view this content.
    and it seems to be working so far! It even works... I believe the term is nested?

    Please Login or Register  to view this content.
    Thank you so much I couldn't have done it without your help
    Reply With QuoteRegister To Reply
  7. 04-08-2014, 04:31 PM #7
    foundy
    foundy is offline
    Registered User
    Join Date
    03-28-2014
    Location
    Hertford, England
    MS-Off Ver
    Excel 2010,2013
    Posts
    38

    Re: How to count a range relative to a dynamic reference

    Glad you got it sorted!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Range.FormulaArray drops relative reference ?
    By SPaquin in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-10-2017, 03:38 PM
  2. [SOLVED] Name Range - Relative Reference
    By Sultix in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-30-2012, 12:11 PM
  3. [SOLVED] Count Formula with Relative Reference
    By amotto11 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-10-2012, 11:39 AM
  4. VBA range Selection with relative reference
    By clemsoncooz in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-29-2011, 12:39 PM
  5. Selecting a range with relative reference
    By HBF in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-12-2008, 04:45 PM

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1