In Excel, checking if a cell matches one item from a named constant string list

Question

This question is an expansion on In Excel, how do I check if a cell is in a list of values (a range of cells) .

I'm trying to do =COUNTIF(name,G6) and when name refers to some cells, it works fine, but when it refers to my constant string array it gives #VALUE!

My constant string array (using the name manager) is ={"192.168.3.2","192.168.3.10"}... etc, and that may be my problem...

Thanks

A good answer to this is in the answers of the same linked question: https://superuser.com/a/838379/150277 — Huseyin Yagli, Aug 22 '21 at 18:05

score 0 · Answer 1 · answered Apr 29 '14 at 12:28

When using multiple criteria for a countif() you might be better off using a COUNTIFS(). e.g.

COUNTIFS(A6:G6,192.168.3.2,A6:G6,192.168.3.10)

if you're defining an array and using numerical values (192.168.3.2), you don't need to use the quotation marks. Those should only be used for text values, unless your lookup array is stored as text. Try it without the quotation marks.

score 0 · Accepted Answer · edited May 23 '17 at 12:41

0

Apologies, but I got my answer from Stack Overflow here https://stackoverflow.com/questions/23293184/in-excel-countif-not-working-with-constant-defined-string?noredirect=1#comment35656073_23293184

I should have just posted there, but I started here because the original question was on Stack Exchange.

Thanks for your response.

edited May 23 '17 at 12:41

Community

1

answered Apr 29 '14 at 15:05

UndeadBob

238

In Excel, checking if a cell matches one item from a named constant string list

2 Answers2