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
Bookmarks