+ Reply to Thread
Results 1 to 5 of 5

Counting non-blanks (CountA) of a matching cell's row

  1. #1
    Registered User
    Join Date
    08-26-2016
    Location
    Hawaii. USA
    MS-Off Ver
    Excel 2010, 2013, 2016
    Posts
    24

    Thumbs up Counting non-blanks (CountA) of a matching cell's row

    Here's probably an easy one for you experts.

    I need to count the non-blank cells in a row. The twist is I'd like the formula to also figure out which row to count from.

    In my sample workbook, I have the "Overview" worksheet, which contains the rows of the table that needs counting. I have a corresponding worksheet for each name in column A of the table (Object 1, Object 2, etc...).

    Looking at one of the worksheets, "Object 2", I can manually find the answer with the following:

    =COUNTA(Overview!B3:H3)

    Thinking about how to automate the row selection, I first turned to index/match, which can easily return the right row, but I couldn't get it to give the CountA a range.

    Then, I thought maybe Offset/Address/Match would work, but excel didn't like that.

    I came across a nested Indirect method that I figured could work, but the following didn't come up with the right result:

    =COUNTA(INDIRECT(B14&":"&C14))

    Which is too bad, because my next step would have been:

    =COUNTA(INDIRECT(ADDRESS(MATCH($A$1,Overview!$A$1:$A$11,0),2,1,TRUE,"Overview")&":"&ADDRESS(MATCH($A$1,Overview!$A$1:$A$11,0),8,1,TRUE,"Overview")))

    In this formula, I have the worksheet name in A1. The Indirect formulas work and return the correct start and end address, but CountA doesn't work. I'm not sure if it's a syntax issue.

    I turn to you, Excel gurus! I'll try to attach my sample workbook.
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,023

    Re: Counting non-blanks (CountA) of a matching cell's row

    Maybe:

    =COUNTA(INDIRECT("'overview'!"&MATCH(A1,Overview!$A$1:$A$11,0)&":"&MATCH(A1,Overview!$A$1:$A$11,0)))-1
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

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

    Re: Counting non-blanks (CountA) of a matching cell's row

    Hi,

    I'd prefer to avoid the volatile INDIRECT if possible:

    =COUNTA(INDEX(Overview!B2:H11,MATCH(A1,Overview!A2:A11,0),0))

    Regards
    Click * below if this answer helped

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

  4. #4
    Registered User
    Join Date
    08-26-2016
    Location
    Hawaii. USA
    MS-Off Ver
    Excel 2010, 2013, 2016
    Posts
    24

    Re: Counting non-blanks (CountA) of a matching cell's row

    Thanks guys. Awesome stuff! Both work, but I agree with Xor LX and will use the cleaner formula.

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,023

    Re: Counting non-blanks (CountA) of a matching cell's row

    It's a fair point!


    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

+ 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. [SOLVED] Using COUNTA to Eliminate Blanks in a Drop Down List?
    By qanjorin in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-17-2016, 07:28 PM
  2. Counting Non Blanks & Blanks Records
    By sathishkm in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-16-2014, 03:29 PM
  3. Replies: 2
    Last Post: 03-01-2013, 07:01 AM
  4. [SOLVED] Count blanks or counta
    By bopsgtir in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-15-2012, 01:43 PM
  5. CountA Counting Formula's
    By realniceguy5000 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-22-2011, 03:19 PM
  6. COUNTA - counting cels which show a value
    By The Monitor in forum Excel General
    Replies: 2
    Last Post: 05-09-2011, 04:18 PM
  7. sum with counta & countif with many matching criteria
    By Ramzes in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-21-2010, 03:56 AM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1