If cell has one value, go to adjacent cell and count

  • Thread starter Lost in Microbiology
  • Start date
L

Lost in Microbiology

I have an imported text file (roughly 30,000lines). What I need is to see if
a column has the value "SAUR" and if so, count the columns with "S" in the
next 7 rows below it and 2 columns to the right.
 
C

Chris Marlow

Hi,

Try,

=IF(RC[-3]="SAUR",COUNTIF(R[1]C[-1]:R[7]C[-1],"S"),0)

I set this up with R1C1 ref style on & put my formula in column 4. My dummy
data was in col 1 (SAUR, <>SAUR) & column 3 (S & <>S). You will need to tweak
if you can't put the formula there.

Regards,

Chris.
 
L

Lost in Microbiology

Chris,

When I paste this into the worksheet, nothing happens. Am I doing something
wrong? Do I run a macro and insert there?

Thanks

Chris Marlow said:
Hi,

Try,

=IF(RC[-3]="SAUR",COUNTIF(R[1]C[-1]:R[7]C[-1],"S"),0)

I set this up with R1C1 ref style on & put my formula in column 4. My dummy
data was in col 1 (SAUR, <>SAUR) & column 3 (S & <>S). You will need to tweak
if you can't put the formula there.

Regards,

Chris.

--
Chris Marlow
MCSD.NET, Microsoft Office XP Master


Lost in Microbiology said:
I have an imported text file (roughly 30,000lines). What I need is to see if
a column has the value "SAUR" and if so, count the columns with "S" in the
next 7 rows below it and 2 columns to the right.
Click to expand...
Click to expand...
 
Top