Search code examples
excelexcel-formulaexcel-2010excel-2007vba

Excel error #value

https://i.sstatic.net/TPyQF.jpg

There is something wrong with my code in MS Excel. If I put "X" in any cell from A1:C10 the value of the D1 will be 1 if not the value will be blank. but there's an error (#VALUE!). Please help me.

Solution

  • Excel is pretty literal and what you are asking it to do, does Range A1:c10 = "X", is not valid. You want to replace 

    A1:C10="X"

    with

    COUNTIF(a1:c10,"X")>0

    i.e.:

    =IF(COUNTIF(A1:C10,"X")>0,"1","")

    One of the many valid ways to accomplish what you are looking to do is to COUNTIF([range],[condition]) and then check if that returns >0.