+ Reply to Thread
Results 1 to 6 of 6

Excel 2007 : Summary data based on some Merged cells and corresponding Pass Fail status

  1. #1
    Registered User
    Join Date
    06-22-2008
    Posts
    5

    Summary data based on some Merged cells and corresponding Pass Fail status

    Hi,
    Request someone to help me get this solved. Attached is an excel workbook with 2 sheets.

    The Summary sheet should reflect the counts based on Data sheet.
    For ex: The values adjacent to Sno1 has Few Pass and few Fail. If any one cell has Fail, then the overall status of Sno1 should be Fail.If all values are Pass then the overall status should be Pass.

    The Summary sheet should reflect the counts based on those values.

    Thanks
    Attached Files Attached Files

  2. #2
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191

    Re: Summary data based on some Merged cells and corresponding Pass Fail status

    I don't understand your layout in the Data sheet, but I put Sno1 in A5 and =IF(COUNTIF(Data!C2:C6,"Pass")=5,"Pass","Fail") in B5. If this is not what you're looking for, could you please update your example to be clearer?

  3. #3
    Registered User
    Join Date
    06-22-2008
    Posts
    5

    Re: Summary data based on some Merged cells and corresponding Pass Fail status

    Quote Originally Posted by darkyam View Post
    I don't understand your layout in the Data sheet, but I put Sno1 in A5 and =IF(COUNTIF(Data!C2:C6,"Pass")=5,"Pass","Fail") in B5. If this is not what you're looking for, could you please update your example to be clearer?
    I am sorry for not being very clear. The formula you provided works fine for my problem. Attached is the updated sheet with Overall status.
    The condition I wanted are as follows:
    1. Each serial no. will Pass only if all Steps Pass
    2. If any step fails then the serial no will fail.
    3. If any step is blank, then the status will be Not executed.

    The above conditions will get me the Summary count of Serial Numbers like Pass, Fail and Not executed status.

    If you look at the final formula I used, I had to give many OR conditions.
    =IF(COUNTIF(Data!C2:C6,"Pass")=5,"Pass",IF(OR(COUNTIF(Data!C2:C6,"Fail")=1,COUNTIF(Data!C2:C6,"Fail")=2,COUNTIF(Data!C2:C6,"Fail")=3,COUNTIF(Data!C2:C6,"Fail")=4,COUNTIF(Data!C2:C6,"Fail")=5),"Fail","NotExecuted"))

    I appreciate if you can let me know if there is any way to know how many conditions to be checked as the number of steps will very. And, any changes to the steps will lead to update the Overall Status formula.

    Thanks for all the help.
    Attached Files Attached Files

  4. #4
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191

    Re: Summary data based on some Merged cells and corresponding Pass Fail status

    I think this should work:
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    06-22-2008
    Posts
    5

    Re: Summary data based on some Merged cells and corresponding Pass Fail status

    Quote Originally Posted by darkyam View Post
    I think this should work:
    Please Login or Register  to view this content.
    Sorry for a late response. I tried the above condition but it works when a user manually enters a space in the cell C for the 3rd point mentioned ("If any step is blank, then the status will be Not executed.")

    How can I use ISNA with the current formula to get the value when blank.

    Note: I entered the formula you suggested in Overall Status column as per my excel sheet.

    Appreciate all the help. Thanks!

  6. #6
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191

    Re: Summary data based on some Merged cells and corresponding Pass Fail status

    Try
    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)

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