Re: Comparing Columns and Counting

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Bob Phillips (bob.phillips_at_notheretiscali.co.uk)
Date: 01/31/05 

Date: Mon, 31 Jan 2005 15:57:52 -0000

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)
"Mike" <Mike@discussions.microsoft.com> wrote in message
news:F2E8C0F4-0B7B-4FC1-B46B-8B247C3F008C@microsoft.com...
> 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.

Relevant Pages