+ 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, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    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.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    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 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    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 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    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. [SOLVED] 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