+ Reply to Thread
Results 1 to 3 of 3

Simple Ifs / CountIf problem :(

Hybrid View

  1. #1
    Registered User
    Join Date
    08-04-2010
    Location
    United States
    MS-Off Ver
    Office 365 for windows
    Posts
    62

    Unhappy Simple Ifs / CountIf problem :(

    Office 365, Excel 2016

    I have a cell range C7:AF7 that will only contain numbers with up to two decimal places.

    I am entering my formula inside cell AH7

    I need my formula to check / do the following:

    If all cells in that range are blank then show blank
    Otherwise count all cells that have a value of 0 or greater and multiply that number of cells by 100.

    This should mean if I enter a 0 inside a cell in that range, the value inside cell AH6 should be 100. If I have 3 cells with 0 the value should be 300. If I add any other number inside any cell within that range the value should then be 400.

    I have this formula so far but it returns NA and doesn't work:

    =IFS(COUNTIF(C7:AF7,"=0"),"100",COUNTIF(C7:AF7,"<>"&""),COUNTIF(C7:AF7,"<>"&"")*100)

    I had this formula originally but it doesn't work when 0 is entered into a cell, it returns #NA

    =IFS(COUNTIF(C7:AF7,">=0")*100=0,"",COUNTIF(C7:AF7,">0")*100>0,COUNTIF(C7:AF7,">0")*100)

    Help is greatly appreciated
    Attached Files Attached Files
    Last edited by lOYvEpi6M87nEoIF0ul8; 07-19-2017 at 10:03 PM.

  2. #2
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 365
    Posts
    2,406

    Re: Simple Ifs / CountIf problem :(

    Perhaps, put this on cell AF2 and copied down as necessary :

    =IF(COUNTIFS(A2:AD2,">=0",A2:AD2,"<>""")=0,"",COUNTIFS(A2:AD2,">=0",A2:AD2,"<>""")*100)

  3. #3
    Registered User
    Join Date
    08-04-2010
    Location
    United States
    MS-Off Ver
    Office 365 for windows
    Posts
    62

    Re: Simple Ifs / CountIf problem :(

    Quote Originally Posted by azumi View Post
    Perhaps, put this on cell AF2 and copied down as necessary :

    =IF(COUNTIFS(A2:AD2,">=0",A2:AD2,"<>""")=0,"",COUNTIFS(A2:AD2,">=0",A2:AD2,"<>""")*100)
    Awesome thank you so much! It works perfectly with a little modification. I ended up using:

    =IF(COUNTIFS(C6:AF6,">=0",C6:AF6,"<>""")=0,"",COUNTIFS(C6:AF6,">=0",C6:AF6,"<>""")*100)

    + Rep and solved :D

+ 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. [SOLVED] Help! Simple Countif problem.
    By windtalkers26 in forum Excel General
    Replies: 2
    Last Post: 01-04-2016, 11:16 AM
  2. [SOLVED] Simple countif formula
    By [email protected] in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-25-2015, 12:42 PM
  3. [SOLVED] Simple countif problem
    By Scotsman89 in forum Excel General
    Replies: 5
    Last Post: 06-07-2014, 05:22 AM
  4. [SOLVED] Simple COUNTIF w <= won't work?
    By SteelMaster in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-15-2014, 10:58 AM
  5. Simple COUNTIF problem
    By Keithflc in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-17-2013, 08:30 AM
  6. Probably Simple Countif but i can't figure it out
    By ssword in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-12-2011, 11:52 AM
  7. simple countif
    By Brian in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-02-2005, 10:06 PM

Tags for this Thread

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