Comparing Columns and Counting

[Guest]

Ok, I have been working through this for the last few hours to no avail.

I have 2 columns of data, driver1 and driver2.

Column 1 (driver1) contains the following (for example)
Row1 - Not Impaired
Row2 - Impaired - Tested
Row3 - Impaired - Not Tested
Row4 - Not Impaired
Row5 - Not Impaired

Column2 (driver2) contains the following.
Row1 - Impaired - Tested
Row2 - Impaired - Tested
Row3 - Not Impaired
Row4 - Not Impaired
Row5 - Impaired - Tested

Each row represents a crash report.

I need to determine the accidents that involved impaired drivers (tested and
untested).

I want to compare the specific row of column1 to column2 to determine if 1
of the 2 drivers was impaired, then count the total.

In this example my output should be 4.

Any help would be appreciated.

Thanks.

 

[Don Guillett]

Have a look in HELP index for COUNTIF

 

[Guest]

I would probably use a third column, assume the data starts in A2 and B2 then
in C2 put

=IF(OR(LEFT(TRIM(A2),8)="Impaired",LEFT(TRIM(B2),8)="Impaired"),1,0)

copy down and then just sum column 3

in one fell swoop

=SUMPRODUCT(--((LEFT(TRIM(A2:A6),8)="Impaired")+(LEFT(TRIM(B2:B6),8)="Impaired")>0))


Regards,

Peo Sjoblom

 

[Bob Phillips]

Mike,

Try this

=SUMPRODUCT(--((ISNUMBER(FIND("impaired",A1:A20)))+(ISNUMBER(FIND("impaired"
,B1:B20))))-(ISNUMBER(FIND("impaired",A1:A20)))*(ISNUMBER(FIND("impaired",B1
:B20))))

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[JulieD]

Mike

one option

=SUMPRODUCT(--((LEFT(A1:A5,8)="Impaired")+(LEFT(B1:B5,8)="Impaired")>0))

Cheers
JulieD

 

[Guest]

Thanks for the help. Adding the additional column will work the best for
this application.

Thanks again.