Is there a way to find a value in a set of cells?

[nealgseattle]

I am trying to find at least one occurence of a specific value in a set of cells that are going to be scattered in a spreadsheet. Here is a pseudocode example what I am trying to do:

If any value in
{a2, a15, b2, b22, c5, c7, c9, d45, d55, e1, e17}
= 1
Then perform this action
Else perform this action

The value could appear more than once, and I am testing for only one occurence.

Here is what I did that doesn't work:
Create a defined name, Range1, of the cells above.
Run this formula:
IF(COUNTIF(Range1,1)>0,1,0)

The COUNTIF will not work with this kind of defined name where the cells are scattered and not in a column or row.

Can someone help?

Thanks,

Neal

[Biff]

Hi!

Try something like this:

SUMPRODUCT(COUNTIF(INDIRECT({"A2","A15","B2","B22"}),1))

Include ALL your cell references inside the Indirect array.

Then wrap that inside an IF:

=IF(SUMPRODUCT(COUNTIF(INDIRECT({"A2","A15","B2","B22"}),1))>0,Do_This,Do_That)

Biff

"nealgseattle" <[email protected]>
wrote in message
news:[email protected]...
>
> I am trying to find at least one occurence of a specific value in a set
> of cells that are going to be scattered in a spreadsheet. Here is a
> pseudocode example what I am trying to do:
>
> If any value in
> {a2, a15, b2, b22, c5, c7, c9, d45, d55, e1, e17}
> = 1
> Then perform this action
> Else perform this action
>
> The value could appear more than once, and I am testing for only one
> occurence.
>
> Here is what I did that doesn't work:
> Create a defined name, Range1, of the cells above.
> Run this formula:
> IF(COUNTIF(Range1,1)>0,1,0)
>
> The COUNTIF will not work with this kind of defined name where the
> cells are scattered and not in a column or row.
>
> Can someone help?
>
> Thanks,
>
> Neal
>
>
> --
> nealgseattle
> ------------------------------------------------------------------------
> nealgseattle's Profile:
> http://www.excelforum.com/member.php...o&userid=35049
> View this thread: http://www.excelforum.com/showthread...hreadid=547933
>

[nealgseattle]

This looks exactly what I need. Thanks for you help!

Neal

[Biff]

You're welcome!

Biff

"nealgseattle" <[email protected]>
wrote in message
news:[email protected]...
>
> This looks exactly what I need. Thanks for you help!
>
> Neal
>
>
> --
> nealgseattle
> ------------------------------------------------------------------------
> nealgseattle's Profile:
> http://www.excelforum.com/member.php...o&userid=35049
> View this thread: http://www.excelforum.com/showthread...hreadid=547933
>