+ Reply to Thread
Results 1 to 6 of 6

Help picking Pass/Fail status text from spreadsheet list

  1. #1
    Registered User
    Join Date
    07-14-2020
    Location
    Hart, MI
    MS-Off Ver
    2010
    Posts
    2

    Help picking Pass/Fail status text from spreadsheet list

    I get safety reports that need data mining, the format is a spreadsheet that is always 10 columns wide (A-J), but varies in length from 26 to 234 lines long, depending on the complexity of the safety audit. The next report is right below it, 5 empty lines separate them, the next report can also be anywhere between 26 and 234 lines long, kind of an endless list for each year of audits. What I need to do is add up how many "P" (pass) and "F" (fail) answers there are in column G. Since the range of reports varies, I cannot use a simple formula to Sum G2:G234 because it may grab information from one or several reports.
    Is there some way to SUM only the G cells between two headers, or between two anything? It would be great to insert a beginning and end border of some kind, and just SUM the data between them, without having to build a formula for every report, and every edit that effects length. The reports come in varying order, different every day, we often have to tweak them. There are a lot of formulas and color formatting on these safety entries, so I cannot send them to a table, excel errors when I try.
    The P & F data gets collected and sent to another sheet, that becomes a weekly report. I am manually counting the the data now.
    I can post examples if needed. Thanks!!

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019
    Posts
    18,753

    Re: Help picking Pass/Fail status text from spreadsheet list

    Please read the yellow banner at the top of this page on how to attach a file.

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,217

    Re: Help picking Pass/Fail status text from spreadsheet list

    Your title and question suggests this is an excel question, but you posted in the Access forum. I will move it for you, please let me know if i was in error?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,217

    Re: Help picking Pass/Fail status text from spreadsheet list

    For the formula, try something like...
    =countif(G:G,""P")

  5. #5
    Registered User
    Join Date
    07-14-2020
    Location
    Hart, MI
    MS-Off Ver
    2010
    Posts
    2

    Re: Help picking Pass/Fail status text from spreadsheet list

    I tried using =COUNTIF('2020 safety audits'!G:G,"P") and it adds up all of the "Passes" from spreadsheets for the whole year. I cannot figure out how to define each audit individually.
    Maybe something like =COUNTIF('2020 safety audits[todays date]'!G:G,"P")

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,217

    Re: Help picking Pass/Fail status text from spreadsheet list

    Perhaps I missed that you needed the count broken down further.

    Try this instead...
    =countifS(G:G,"P",H:H,"other-criteria")

    If that doesnt work, please follow the directions in the yellow banner above and upload a sample workbook.

+ 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. Replies: 6
    Last Post: 01-10-2018, 01:31 AM
  2. PASS or FAIL
    By HansWitt in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-18-2015, 06:00 AM
  3. VBA Status of Pass/Fail Required on Userform
    By Dereshp in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-12-2014, 11:57 PM
  4. [SOLVED] Need a formula for pass/fail based on no. of Y answers but automatically fail if 3a is N
    By Researcher1111 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 09-15-2013, 10:20 PM
  5. text value pass or fail
    By wykoems in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-19-2013, 08:41 AM
  6. Replies: 5
    Last Post: 03-16-2010, 07:05 AM
  7. How to calculate pass/fail percentages entered on a spreadsheet?
    By Jenna in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 08-05-2006, 12:35 PM

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