Getting error with formula..COUNTIF

Question

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.

detlef_lewin · Answer

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

aamu21 · Answer

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

detlef_lewin · Answer

aamu21&nbsp;You can use your original formula or SEARCH() or LEFT().&nbsp;

peterbartholomew1 · Answer

aamu21&nbsp;Following&nbsp;Detlef_Lewin&nbsp;'s suggestion= LET(
    format, MAP(amount, LAMBDA(a, CELL("format", a))),
    IF(LEFT(format, 1) = "C", "Y", "N")
  )

Forum Discussion

Getting error with formula..COUNTIF

Share

Resources