+ Reply to Thread
Results 1 to 4 of 4

Index Matching & CountA function

  1. #1
    Registered User
    Join Date
    06-14-2017
    Location
    Atlanta,
    MS-Off Ver
    Microsoft Excel 2016 MSO
    Posts
    54

    Index Matching & CountA function

    Please find workbook attached.
    Help 1.xlsx
    Hi all,

    Ok here's what I'm trying to do. On the vacancies tab I have five core positions and 10 possible other vacant positions. I need a function to look up the ID, find that ID on the "sheet" tab and count 1 if that position is vacant or not (by counting whether or not there is text in that field).

    The function I currently have written works well ONLY IF the index matching is able to find the code see row 7, tab "Vacancies". Row seven is an example of the current function returning the right values.

    However, the issue becomes when the function cannot find the referenced ID on the sheet tab. In this case, the function returns a value that makes it appear as if individual programs all have 14 vacancies, instead of returning a value of 0 because the ID was not found.

    I'm sorry this is a little technical and maybe confusing, but any help you could provide are GREATLY appreciated.

    Again, see workbook attached to play with.

  2. #2
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Index Matching & CountA function

    I don't think I'm entirely clear on what you're requesting, but if my understanding that you want to fix the counting in C3:G3 is correct, try the following formula in C3:

    =COUNTIFS(Sheet!X$3:X$100,"<>"&"",Sheet!$J$3:$J$100,$A3)

    Fill right and then down. I'm not clear on what's supposed to be happening in column H, so if that's the issue, please elaborate. I'm also not clear on why X:AB is repeated in AC:AG, but your existing formulas looked only at X:AB, so mine do as well. Take a look at the attachment to see if C:G are calculating as desired:
    Attached Files Attached Files
    If your problem has been solved, please use "Thread Tools" to mark the thread as "Solved".

    If you're grateful for this site's existence and would like to contribute to the community, please consider posting something hilarious in our joke thread:
    https://www.excelforum.com/the-water...ke-thread.html

  3. #3
    Registered User
    Join Date
    06-14-2017
    Location
    Atlanta,
    MS-Off Ver
    Microsoft Excel 2016 MSO
    Posts
    54

    Re: Index Matching & CountA function

    Hi - Thank you for your message.

    Let me clarify. On my end, the two tabs, vacancies and sheet, are actually two different workbooks. The sheet tab is just data in any order. The vacancies workbook is just returning results from the sheet workbook to facilitate other analysis.

    I am seeking a function that will look up the ID in the vacancies workbook (cells A3:A32), look up that code in the sheet workbook (cells J1:J100), and return a value of 1 if there is any text found on the same row as the ID found in cells J1:J00, in column X1:100. The data in columns x1:x100 is text, not numbers, as you can see.

    My current function (counta + index/matching) returns a positive value if it cannot find the ID to index match, which is a false positive. It should return a 0 value because there was no code to reference, and hence, no vacancy for that program. Index matching is necessary because the id might be in any order in the raw data file (sheet tab/workbook).

    Your function works well, but only if both workbooks are open. Primarily I wont be using the sheets tab at all, its just raw data. If I use your function and only have one workbook open, the vacancies workbook, it returns a #Value error. I didn't upload two workbooks at first because honestly, I didn't know it would make a difference.

    Sorry this is confusing but thank you for the help!!!!

  4. #4
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Index Matching & CountA function

    Ah, that's a bit clearer. It sounds like you could smooth out the links between the workbooks. Here's a better explanation than I can provide of the various ways to control external link updates:

    https://support.office.com/en-us/art...3-dd357fe0e653

    I don't have extensive experience with linked workbooks, so I don't know how troublesome it is to have a formula rely on a reference to an external (closed) source. If the formulas aren't working through the link the way that you want, it might be worth your time to create a "Sheet" sheet in the same workbook as your "Vacancies" sheet and tie that sheet to your external raw data page. That way instead of having the formula pull from a linked workbook, the formula is pulling from another sheet in the same workbook that merely mimics the data from the linked book. That approach might make your updates cleaner.

+ 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] Index Matching + ranged CountA
    By UHD in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 03-23-2021, 01:59 PM
  2. Counting non-blanks (CountA) of a matching cell's row
    By hawaean in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-05-2017, 07:34 AM
  3. Vlookup in conjunction with Index and CountA function
    By manning457 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-27-2015, 02:26 PM
  4. [SOLVED] IF & INDEX Formula =INDEX(D11:D59,COUNTA(D11:D59),1)
    By GC1 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-05-2013, 08:11 AM
  5. Replies: 4
    Last Post: 12-27-2012, 06:18 PM
  6. Index Matching with a nested Greater than function for multiple criteria
    By Solpics in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-05-2012, 12:22 AM
  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