| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
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. |
|
#2
|
|||
|
|||
|
|
|
#3
|
|||
|
|||
|
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. |
|
#4
|
|||
|
|||
|
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. |
|
#5
|
|||
|
|||
|
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. |
|
#6
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Counting the Contents of Two Columns | Excel Discussion (Misc queries) | |||
| counting in one column when two expressions in two other columns are true | Excel Worksheet Functions | |||
| Counting in arrays | Excel Worksheet Functions | |||
| Counting columns and specific Dates | Excel Worksheet Functions | |||
| counting entries between two dates? | Excel Worksheet Functions | |||
Linear Mode
