+ Reply to Thread
Results 1 to 8 of 8

formula that counts the number of apoointed cells with value 100%

  1. 01-03-2017, 03:48 AM #1
    erkamu
    erkamu is offline
    Forum Contributor
    Join Date
    01-20-2011
    Location
    liverpool
    MS-Off Ver
    Excel 2016
    Posts
    198

    formula that counts the number of apoointed cells with value 100%

    Hello,
    I have an xls where we are tracking compliance of measures to target.
    For each measure I have actual value (cell c2), target value (cell c3) and compliance in cell c4(=c2/c3).
    For next measure I have the same in cells c 5,6,7.

    What I am interested is in the bottom of the column C to have:
    * a formula 1 that counts the number of "compliance cell that have value >=100%
    * a formula 2 that counts the total number of measures that doesn't matter what is the compliance

    A tricky part in this file is that in some cases I could have actual value for a measure not populated...so in this case compliance will be 0 but I would like both formulas to ignore that measure from calculation.

    In the attachment you can find sample file.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 01-03-2017, 04:40 AM #2
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,940

    Re: formula that counts the number of apoointed cells with value 100%

    Your sample data makes no sense. For formula 1 you say that the answer should be three, but there is only one measure that is >=100%. For that you need:

    =COUNTIFS($B$2:$B$13,"compliance",$C$2:$C$13,">=100%")

    Formula 2 is counting what, exactly? Which two values are you counting?
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 01-03-2017, 05:11 AM #3
    avk
    avk is offline
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Microsoft Office 2013
    Posts
    3,223

    Re: formula that counts the number of apoointed cells with value 100%

    I think required like this. In "C15"
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    atul

    If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

    Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".
    Reply With QuoteRegister To Reply
  4. 01-03-2017, 07:15 AM #4
    erkamu
    erkamu is offline
    Forum Contributor
    Join Date
    01-20-2011
    Location
    liverpool
    MS-Off Ver
    Excel 2016
    Posts
    198

    Re: formula that counts the number of apoointed cells with value 100%

    AliGW,
    sorry for the confusion that I have created.
    Formula 1 is counting number of compliance cell that have value >=100% so value in sample file should be 1
    Formula 2 is counting total number of compliance cell...does not matter what the value is...But that formula should ignore measure that are not having actual populated. So value in sample file should be 3
    Reply With QuoteRegister To Reply
  5. 01-03-2017, 01:06 PM #5
    JeteMc
    JeteMc is offline
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,570

    Re: formula that counts the number of apoointed cells with value 100%

    Try using the following for 'Formula 2':
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
    Reply With QuoteRegister To Reply
  6. 01-10-2017, 09:08 AM #6
    erkamu
    erkamu is offline
    Forum Contributor
    Join Date
    01-20-2011
    Location
    liverpool
    MS-Off Ver
    Excel 2016
    Posts
    198

    Re: formula that counts the number of apoointed cells with value 100%

    Suggested foirmula 1 of AliGW worked perfectly fine.
    Suggested foirmula 2 of JeteMc worked perfectly fine.
    THANK YOU!!!
    Reply With QuoteRegister To Reply
  7. 01-10-2017, 09:56 AM #7
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,940

    Re: formula that counts the number of apoointed cells with value 100%

    You're welcome!
    Reply With QuoteRegister To Reply
  8. 01-10-2017, 12:49 PM #8
    JeteMc
    JeteMc is offline
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,570

    Re: formula that counts the number of apoointed cells with value 100%

    You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. create a variable i that counts number of cells in a range that start with 0
    By ammartino44 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-21-2015, 08:30 PM
  2. Need a formula that counts the number of days listed in cells between a set range of dates
    By gaffer in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-21-2014, 02:30 PM
  3. [SOLVED] Formula that counts the number of numbers in a cell.
    By Rixonomic in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-16-2013, 05:32 PM
  4. I need a formula the counts the number of cells that meet 2 conditions ac multiple w/sheet
    By clemmy75 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-28-2012, 01:43 PM
  5. Formula that counts cells and returns a number
    By blburden in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-20-2012, 10:43 PM
  6. Is there a function that counts the number of non-blank cells in a range?
    By xibalba in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-23-2012, 04:09 AM
  7. a function that counts the number of cells with information
    By zuri125 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-07-2006, 02:40 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1