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

  • Thread starter Thread starter nealgseattle
  • Start date Start date
N

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
 
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]...
 
Back
Top