+ Reply to Thread
Results 1 to 19 of 19

Help with offest and countif (Dynamic range)

  1. #1
    Registered User
    Join Date
    11-16-2013
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    46

    Help with offest and countif (Dynamic range)

    Hi I really need some help with this formula to produce a dynamic named range

    =offset(A1,,,countif(a1:a10,"?"),7)

    THE "?*" excludes cells with "" null
    every time I put it in a named range it works however when I refer to the named ranged in a pivot table it says an not find source

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Help with offest and countif (Dynamic range)

    Maybe try:

    =offset(A1,,,countif(a1:a10,"<>"&""),7)
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!

  3. #3
    Registered User
    Join Date
    11-16-2013
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    46

    Re: Help with offest and countif (Dynamic range)

    Ok it's just I'm trying to make a dynamic range which will ignore blanks cells even with formulas in however although the formula in the cell is not displaying anything it is still counting it when I use the offset with the counta function

  4. #4
    Registered User
    Join Date
    11-16-2013
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    46

    Re: Help with offest and countif (Dynamic range)

    So will this formula you adjusted for me exclude cell which are blank and with "" formulas ?

  5. #5
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Help with offest and countif (Dynamic range)

    I had some issues using your count so I went with sumproduct instead to skip the null values.


    I made a range A1:B10 with a bunch of formulas. Some returned values, some returned "" null values.

    deathpivot:
    =OFFSET(Sheet2!$A$1,,,SUMPRODUCT(--(Sheet2!$A$1:$A$10<>"")),2)

    and then pulled my data from deathpivot and it worked fine.

    Make sure to include the name of the tabs in your references and adjust the scope of the named range to Workbook in the name manager. Otherwise it will never find the name when you make the pivot in another tab.

  6. #6
    Registered User
    Join Date
    11-16-2013
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    46

    Re: Help with offest and countif (Dynamic range)

    Ok I'll give it a try

  7. #7
    Registered User
    Join Date
    11-16-2013
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    46

    Re: Help with offest and countif (Dynamic range)

    im still getting an error do you have an email I could send you a copy of my workbook ?

  8. #8
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Help with offest and countif (Dynamic range)

    Here's my work; you can take it apart and see how it matches up to yours.

    I've been at the office for almost 12 hours, so I'm leaving soon. I don't have Excel at home.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    11-16-2013
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    46

    Re: Help with offest and countif (Dynamic range)

    ok thanks and that is a long time

  10. #10
    Registered User
    Join Date
    11-16-2013
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    46

    Re: Help with offest and countif (Dynamic range)

    may I just ask if my sheet Is called History do I still have to put History! when referring to it in a formula or just History?

  11. #11
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Help with offest and countif (Dynamic range)

    You get used to it. Or go crazy.

    I crunch numbers all day in Access and Excel; it's about as close to a rockstar lifestyle as this geek will get.


    =OFFSET(History!$A$1,,,SUMPRODUCT(--(History!$A$1:$A$10<>"")),2)

    or something like that.

  12. #12
    Registered User
    Join Date
    11-16-2013
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    46

    Re: Help with offest and countif (Dynamic range)

    Wow access and excel ...and thanks

  13. #13
    Registered User
    Join Date
    11-16-2013
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    46

    Re: Help with offest and countif (Dynamic range)

    Mate im still getting it wrong I will have a look in the morning thanks for your help tho

  14. #14
    Forum Contributor
    Join Date
    02-20-2014
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    170

    Re: Help with offest and countif (Dynamic range)

    Wouldn't you be better using =INDEX(History!$1:$9999,1,1):INDEX(History!$1:$9999,countif(History!$A$1:$A$10,"<>"&""),8)

    That way it isn't volatile. Not an issue if you aren't using a big dataset, but if you include a few dynamic ranges in a larger file the offset formulas will slow Excel right down.

  15. #15
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Help with offest and countif (Dynamic range)

    Maybe this...

    =OFFSET(A1,,,SUMPRODUCT(--(LEN(A1:A10)>0)),7)
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  16. #16
    Registered User
    Join Date
    11-16-2013
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    46

    Re: Help with offest and countif (Dynamic range)

    I just keep getting a message saying data source is not valid I have gone into name manager and named the range and put the formula in however when I got to make the pivot table I type the name in and it say data source is not valid

  17. #17
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Help with offest and countif (Dynamic range)

    Hi,

    I think you'd be best advised to upload the actual workbook, then. Obviously replace any confidential/sensitive information with dummy data if necessary.

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  18. #18
    Registered User
    Join Date
    11-16-2013
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    46

    Re: Help with offest and countif (Dynamic range)

    Thanks guys I good it work thank you all I have given all reputation

  19. #19
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Help with offest and countif (Dynamic range)

    You're welcome. We appreciate the feedback!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Using COUNTIF with a dynamic range
    By ruthjames in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-25-2013, 11:20 AM
  2. [SOLVED] COUNTIF Function with Dynamic Range
    By rzrbkpk in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-02-2013, 04:07 AM
  3. COUNTIF: dynamic range
    By BATISTAJM in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-22-2013, 07:17 PM
  4. Dynamic Range using Offset and CountIF
    By mark_jam3s in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-04-2008, 11:43 AM
  5. Offset, Dynamic range, Countif
    By Bryce in forum Excel General
    Replies: 3
    Last Post: 10-26-2005, 08:05 AM

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