Help with formula!

Question

Because I wanted to check if a symbol contains "/*" but it would only bring back a #VALUE! error.

![](https://learn-attachment.microsoft.com/api/attachments/12b36a63-ba80-46d0-9313-39d061104d51?platform=QnA

Answer 1

PS.... I wrote:

I think the #VALUE error is coming from the INDEX expression that you redacted.

But it is not the use of INDEX per se. To demonstrate the correct use of INDEX:

Formulas:

B1 (copy down): =COUNTIF(A1,"*/~**")

C1: =COUNTIF(A1:A3,"*/~**")

D1: =COUNTIF(INDEX(A1:B3,0,1),"*/~**")

E1:E3 (array-enter): =INDEX(A1:B3,0,1)

B1:B3 demonstrate the correct counting for A1:A3 individually.

C1 demonstrates the correct counting for the range A1:A3.

D1 demonstrates that COUNTIF permits the use of INDEX to return the subset range A1:A3, selected from the larger range A1:B3.

E1:E3 demonstrate that INDEX(A1:B3,0,1) correctly specifies the subset range A1:A3 by returning, in that context, the values in A1:A3.

Answer 2

Just some thoughts....

I think the #VALUE error is coming from the INDEX expression that you redacted.

I have encounter no Excel error when I enter a/*b into A1, then try =COUNTIF(A1, "/*").

But for that example, COUNTIF returns zero unintentionally. That is because the COUNTIF condition "says": count cells that start with slash followed by zero or more characters, since "*" is a wildcard character in that context.

You could write =COUNTIF(A1,"*/~**"). But that's a mouthful.

Alternatively, you could write =SUMPRODUCT(--ISNUMBER(SEARCH("/*", A1)))

Errata.... My bad! Use F IND instead SEARCH. SEARCH supports the same wildcard characters that COUNTIF does. FIND does not. FIND will work because your "find_text" does not contain alphabetic characters (a through z). But if it, beware that FIND is case-sensitive.

Of course, you can replace A1 with the intended range.

But I think that is where you are having the #VALUE problem.

Can't help with that because it is redacted.

Answer 3

Well, it took sometime for me to digest your answers!  Hehe.

And I guess I would agree with you that the VALUE error was caused by the INDEX function.

So that forced me to add a column to separately use the index to get the value!

So I’ll still mark your answer for the alternatives you gave.

Answer 4

As I see joeu2004 supposedly with a last reply in the profile screen but I couldn’t seem find it here.