+ Reply to Thread
Results 1 to 4 of 4

Countif, based on 2 requirements, returning value to another worksheet

  1. #1
    Forum Contributor 00Able's Avatar
    Join Date
    11-17-2010
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    244

    Lightbulb Countif, based on 2 requirements, returning value to another worksheet

    Ok, Heres a Multi criteria Count if Statement, I have an error log and a production sheet.
    What I want is if the error log reports an error, I want it to return the error to the production log for the correct date. So basically it would be count if, the Lines are = and the Dates are =.

    For a better view I have attached a worksheet, illustrating my issue and what I would like to accomplish., In sheet 1 "Production" I have a field named "errors", I would like a count if statement to pull the information from sheet 2 "Errors".

    Each Row in Sheet 2 is one error, so if Columns "Date" & "Line" in sheet Errors = Columns "Date" & "Line" in sheet Production, then it should count each item and add them in Column H in the Production Sheet. I hope this makes sense, all I want is the total amount of errors for each line to automatically calculate, based on what is inputted into the Error Sheet. Which is kept on a different workbook, but I have combined it so you can view.
    Last edited by 00Able; 11-28-2010 at 06:59 PM.

  2. #2
    Forum Contributor
    Join Date
    07-05-2006
    Location
    Canada
    MS-Off Ver
    2003, 2007
    Posts
    581

    Re: Countif, based on 2 requirements, returning value to another worksheet

    Hi,

    I provide no guarantees with respect to referencing other workbooks (not terribly experienced with it and tend to avoid it when at all possible). That said, here's something you can try.

    You could do this by setting up two dynamic named ranges in your Errors workbook. http://www.contextures.com/xlNames01.html#Dynamic describes how to do this. One should be called ErrorDate and refer to column A, and the other should be called ErrorLine and refer to column B. (Names are up to you really, but I'll use the above names below, so if you change them, be consistent.)

    In your Production workbook, instead of merging A2:A9, have them each separate with the date in each field. (ie. they all say 12/1/2010 for the current date, 12/2/2010 for the next day, etc)

    Then, in the Production workbook, add two named ranges as follows:
    • ErrorDate: =[Errors.xls]Sheet1!ErrorDate
    • ErrorLine: =[Errors.xls]Sheet1!ErrorLine

    Then the following formula should work in Column H:
    PHP Code: 
    =SUMPRODUCT(--(Date=A2),--(Line=B2)) 
    No guarantees, but this should give you something to start with. Someone else may have a better suggestion.

    S

  3. #3
    Forum Contributor 00Able's Avatar
    Join Date
    11-17-2010
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    244

    Re: Countif, based on 2 requirements, returning value to another worksheet

    Sorry I just realized that I forgot to save the file before I closed it and attached the previous version. I have included both sheets on the same workbook, so it should be pretty transparent as to what I am wanting.

    I was hoping if I used the I could use the countif function, by matching the areas that needs to be the same...
    Please Login or Register  to view this content.
    However, it is not working like I wish, maybe I need to array enter it, maybe I am handling this issue all wrong...
    Last edited by 00Able; 12-05-2010 at 07:29 PM.

  4. #4
    Forum Contributor 00Able's Avatar
    Join Date
    11-17-2010
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    244

    Re: Countif, based on 2 requirements, returning value to another worksheet

    Well hopefully this helps someone, but I figured out my issue, I need to provide the following equation using "shift control enter"

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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