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

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

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

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

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

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

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

Any ideas?

Cheers

Andy


--
Andyd74
------------------------------------------------------------------------
Andyd74's Profile: http://www.excelforum.com/member.php...o&userid=34013
View this thread: http://www.excelforum.com/showthread...hreadid=545414

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

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


HTH
--
AP

"Roger Govier" a écrit dans le message de
news: ...
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" wrote in
message ...

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

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

Thanks, thats working perfect now

Cheers

Andy


--
Andyd74
------------------------------------------------------------------------
Andyd74's Profile: http://www.excelforum.com/member.php...o&userid=34013
View this thread: http://www.excelforum.com/showthread...hreadid=545414

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

Thanks Ardus, typed in too much haste!!!

--
Regards

Roger Govier


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


HTH
--
AP

"Roger Govier" a écrit dans le
message de news: ...
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"
wrote in message
...

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