function to count the amount of cells depending on part of the cell entry

[Andyd74]

I want to count the number of "passes" from a column based on the criteria that the adjacent column cells contain the letters "AB" at the start of what ever is in the cell.

i.e.

column A column B
AB xxx pass
AB yyy pass
BC zzz pass
BC xxx fail
AB yyy fail

etc

Any ideas

thanks

[Roger Govier]

Hi Andy

one way
=SUMPRODUCT(--(LEFT($A$1:$A$100="AB"),--($B$1:$B$100="Pass"))
Change ranges to suit but ensure that they are of equal length.
--
Regards

Roger Govier


"Andyd74" <[email protected]> wrote
in message news:[email protected]...
>
> I want to count the number of "passes" from a column based on the
> criteria that the adjacent column cells contain the letters "AB" at
> the
> start of what ever is in the cell.
>
> i.e.
>
> column A column B
> AB xxx pass
> AB yyy pass
> BC zzz pass
> BC xxx fail
> AB yyy fail
>
> etc
>
> Any ideas
>
> thanks
>
>
> --
> Andyd74
> ------------------------------------------------------------------------
> Andyd74's Profile:
> http://www.excelforum.com/member.php...o&userid=34013
> View this thread:
> http://www.excelforum.com/showthread...hreadid=545414
>

[Andyd74]

Thanks, but it didnt quite work, giving me a formula error

Any ideas?

Cheers

Andy

[Ardus Petus]

Typo and Argument missing for LEFT!
Try:
=SUMPRODUCT(--(LEFT($A$1:$A$100,2)="AB"),--($B$1:$B$100="Pass"))


HTH
--
AP

"Roger Govier" <[email protected]> a écrit dans le message de
news: [email protected]...
> Hi Andy
>
> one way
> =SUMPRODUCT(--(LEFT($A$1:$A$100="AB"),--($B$1:$B$100="Pass"))
> Change ranges to suit but ensure that they are of equal length.
> --
> Regards
>
> Roger Govier
>
>
> "Andyd74" <[email protected]> wrote in
> message news:[email protected]...
>>
>> I want to count the number of "passes" from a column based on the
>> criteria that the adjacent column cells contain the letters "AB" at the
>> start of what ever is in the cell.
>>
>> i.e.
>>
>> column A column B
>> AB xxx pass
>> AB yyy pass
>> BC zzz pass
>> BC xxx fail
>> AB yyy fail
>>
>> etc
>>
>> Any ideas
>>
>> thanks
>>
>>
>> --
>> Andyd74
>> ------------------------------------------------------------------------
>> Andyd74's Profile:
>> http://www.excelforum.com/member.php...o&userid=34013
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=545414
>>
>
>

[Andyd74]

Thanks, thats working perfect now

Cheers

Andy

[Roger Govier]

Thanks Ardus, typed in too much haste!!!

--
Regards

Roger Govier


"Ardus Petus" <[email protected]> wrote in message
news:[email protected]...
> Typo and Argument missing for LEFT!
> Try:
> =SUMPRODUCT(--(LEFT($A$1:$A$100,2)="AB"),--($B$1:$B$100="Pass"))
>
>
> HTH
> --
> AP
>
> "Roger Govier" <[email protected]> a écrit dans le
> message de news: [email protected]...
>> Hi Andy
>>
>> one way
>> =SUMPRODUCT(--(LEFT($A$1:$A$100="AB"),--($B$1:$B$100="Pass"))
>> Change ranges to suit but ensure that they are of equal length.
>> --
>> Regards
>>
>> Roger Govier
>>
>>
>> "Andyd74" <[email protected]>
>> wrote in message
>> news:[email protected]...
>>>
>>> I want to count the number of "passes" from a column based on the
>>> criteria that the adjacent column cells contain the letters "AB" at
>>> the
>>> start of what ever is in the cell.
>>>
>>> i.e.
>>>
>>> column A column B
>>> AB xxx pass
>>> AB yyy pass
>>> BC zzz pass
>>> BC xxx fail
>>> AB yyy fail
>>>
>>> etc
>>>
>>> Any ideas
>>>
>>> thanks
>>>
>>>
>>> --
>>> Andyd74
>>> ------------------------------------------------------------------------
>>> Andyd74's Profile:
>>> http://www.excelforum.com/member.php...o&userid=34013
>>> View this thread:
>>> http://www.excelforum.com/showthread...hreadid=545414
>>>
>>
>>
>
>