Getting error with formula..COUNTIF

Copper Contributor

I would to use a formula to create a flag for a column by determining if the cell format is Currency or Percentage. For this example, IF the cell format is Currency, return Y ELSE N. 

1. I will use the CELL function passing the format argument to determine the cell type. =CELL("format", B1). I've seen this return several values but for the Currency column they start with 'C'....C, C0, C1....As a result I need to use the wildcard.

2. To leverage wildcard functionality and be able to have "C*", I believe I need to use the COUNTIF function. eg.=IF(COUNTIF(B2, "C*"),"Y","N"). When I invoke the formula in 1 and store the value in B2, the preceding formula works like a charm.


However, I would like to avoid having to create a separate column to achieve that. I would like to call the CELL function inside of the COUNTIF function. But I get an error with the formula:

=IF(COUNTIF(CELL("format", B1), "C*"),"Y","N")


Here is the error message:
"There's a problem with this formula.

Not trying to type a formula?

When the first character is an equal ("=") or minus ("-") sign, Exel thinkis it's a formula..........


Is my syntax incorrect or is this not possible? Thanks in advance.

4 Replies


COUNTIF() needs a cell-/range-reference as a first argument. Anything else will be rejected.


Thanks. Are there any suggestions as to how to accomplish this?


You can use your original formula or SEARCH() or LEFT().



Following @Detlef Lewin 's suggestion


= LET(
    format, MAP(amount, LAMBDA(a, CELL("format", a))),
    IF(LEFT(format, 1) = "C", "Y", "N")