+ Reply to Thread
Results 1 to 8 of 8

Counting Certain Cells in a Table

  1. #1
    Registered User
    Join Date
    11-07-2014
    Location
    London
    MS-Off Ver
    Office 2010
    Posts
    9

    Counting Certain Cells in a Table

    I have a table in excel which contains a list of tests I run. These tests are part of an overall Run and there are 3 columns (Pass, Total, Percentage) that I add. I add these for each new run. The percentage column is the important one for me. I have 2 columns at the end of the table to count the number of tests that passed and failed for each row. If the % is 100% its marked as a pass. Anything less is a fail. See my attached file for an example of the table:

    sample excel.xlsx

    My question; is there any formula I can use which would automatically count the results for the columns 'Number of Tests Passed' and 'Number of Tests Failed'?

    Let me know if I need to explain the table any further!

    Thanks!

  2. #2
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Counting Certain Cells in a Table

    Using your posted workbook....
    This formula returns the count of Passed runs:
    Please Login or Register  to view this content.
    and this formula returns the count of Failed runs
    Please Login or Register  to view this content.
    Copy those formulas down as far as you need.

    Is that something you can work with?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Valued Forum Contributor
    Join Date
    10-13-2010
    Location
    Sunderland, England
    MS-Off Ver
    Excel 2007 (Home) / 2003 (Work)
    Posts
    740

    Re: Counting Certain Cells in a Table

    =COUNTIFS($1:$1,"Run*",3:3,1)
    in K3
    and
    =COUNTIFS($1:$1,"Run*",3:3,"<>"&1)
    in L3

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2405
    Posts
    13,417

    Re: Counting Certain Cells in a Table

    This way is a bit more primitive.

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    and
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Dave

  5. #5
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,915

    Re: Counting Certain Cells in a Table

    L3
    Please Login or Register  to view this content.
    M3
    Please Login or Register  to view this content.
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  6. #6
    Registered User
    Join Date
    11-07-2014
    Location
    London
    MS-Off Ver
    Office 2010
    Posts
    9

    Re: Counting Certain Cells in a Table

    Thanks for the feedback guys. Plenty of different options that worked for me so much appreciated :-)

    Just one final question though....

    So at the moment these are the formulas Im using:
    =COUNTIFS($1:$1,"Run*",3:3,1)
    and
    =COUNTIFS($1:$1,"Run*",3:3,"<>"&1)

    The second formula counts anything less than 1. Is it possible to change this to count anything greater than zero but less than 1? Because sometimes I will have blank cells and I dont want them to be counted as failures!

  7. #7
    Registered User
    Join Date
    11-07-2014
    Location
    London
    MS-Off Ver
    Office 2010
    Posts
    9

    Re: Counting Certain Cells in a Table

    Does anyone know if this is possible?

  8. #8
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2405
    Posts
    13,417

    Re: Counting Certain Cells in a Table

    This change to your formula will take care of the blank cells, but not zeros because they are not blank.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Do you also want to exclude zeros?

+ 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. Counting blank cells in a pivot table
    By perkinsl in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 07-16-2015, 04:58 PM
  2. HELP: Counting the number of cells in with values in a large table
    By tyler3135 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-14-2014, 08:32 PM
  3. Replies: 3
    Last Post: 03-01-2012, 06:17 AM
  4. Counting number of occurences specific date in range of cells in a table
    By jzzman in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-17-2009, 01:47 AM
  5. pivot table counting blank cells in data table
    By nicko54 in forum Excel General
    Replies: 4
    Last Post: 09-23-2008, 11:26 AM
  6. Replies: 2
    Last Post: 09-23-2008, 11:01 AM
  7. [SOLVED] Counting Non-Zero Cells in a Pivot Table
    By Moz in forum Excel General
    Replies: 0
    Last Post: 07-30-2006, 09:00 AM

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