Comparing Columns and Counting
 

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.

Have a look in HELP index for COUNTIF

--
Don Guillett
SalesAid Software

"Mike" wrote in message
...
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.

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

"Mike" wrote:

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.

Mike,

Try this

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

--

HTH

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


"Mike" wrote in message
...
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.

Mike

one option

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

Cheers
JulieD


"Mike" wrote in message
...
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.

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

Thanks again.

"Peo Sjoblom" wrote:

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

"Mike" wrote:

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.