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

A

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
 
R

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.
 
A

Ardus Petus

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


HTH
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Some more help to edit some formulas 6
How do I create a formula using part of the data in 1 cell? 3
Changing the value of cell 1
Selecting the correct number from a range of cells 8
Complicated counting of cells (based on other cells contents) 3
Formula Result is "V6", need Excel to use cell "V6", not the resul 3
How do I count cells that contain a number of charctors. 3
display part of the cell content 5

Top