I have the following problem and don't mind which solution is forthcoming:

Sheet1 contains data about claims made to our department (approx 700 rows).

Column A contains the claim reference number (5 digits) - there can be up to
50 rows with the same reference number.

Column D is either 'Adult' or 'Veal'.

Column F is either 'Yes' or 'No' to say if the animal is satisfactory.

What I need is the number of 'Adult' and 'Veal' claims that are
satisfactory. A satisfactory claim is defined as one where all animals in a
claim are 'Yes' in column F.

Thanks in advance.

Gareth

try
=sumproduct((rngA=12345)*(rngD={"Adult","Veal"})*( rngF="Yes"))

"Gareth" wrote in message
...
I have the following problem and don't mind which solution is forthcoming:

Sheet1 contains data about claims made to our department (approx 700
rows).

Column A contains the claim reference number (5 digits) - there can be up
to
50 rows with the same reference number.

Column D is either 'Adult' or 'Veal'.

Column F is either 'Yes' or 'No' to say if the animal is satisfactory.

What I need is the number of 'Adult' and 'Veal' claims that are
satisfactory. A satisfactory claim is defined as one where all animals in
a
claim are 'Yes' in column F.

Thanks in advance.

Gareth

the problem with this is that I don't know the the claim numbers'

Example:

25 animals with the same ref number, they are all Adult and all are Yes -
this would be 1 satisfactory claim.

41 animals with another refernce number (but the same), all Adult but with 2
No's - this shouldn't be counted as a satifacctory claim.

I think what I want to do is count the number of animals in a claim, if all
are yes then count it as 1.

Gareth

"Don Guillett" wrote in message
...
try
=sumproduct((rngA=12345)*(rngD={"Adult","Veal"})*( rngF="Yes"))

"Gareth" wrote in message
...
I have the following problem and don't mind which solution is
forthcoming:

Sheet1 contains data about claims made to our department (approx 700
rows).

Column A contains the claim reference number (5 digits) - there can be
up
to
50 rows with the same reference number.

Column D is either 'Adult' or 'Veal'.

Column F is either 'Yes' or 'No' to say if the animal is satisfactory.

What I need is the number of 'Adult' and 'Veal' claims that are
satisfactory. A satisfactory claim is defined as one where all animals
in
a
claim are 'Yes' in column F.

Thanks in advance.

Gareth

You need to use IF, AND Function, send me a sample of the
spreadsheet and I'll enter the formula for you.

Jez
-----Original Message-----
the problem with this is that I don't know the the claim
numbers'

Example:

25 animals with the same ref number, they are all Adult
and all are Yes -
this would be 1 satisfactory claim.

41 animals with another refernce number (but the same),
all Adult but with 2
No's - this shouldn't be counted as a satifacctory claim.

I think what I want to do is count the number of animals
in a claim, if all
are yes then count it as 1.

Gareth

"Don Guillett" wrote in message
...
try
=sumproduct((rngA=12345)*(rngD={"Adult","Veal"})*
(rngF="Yes"))

"Gareth" wrote in
message
...
I have the following problem and don't mind which
solution is
forthcoming:

Sheet1 contains data about claims made to our
department (approx 700
rows).

Column A contains the claim reference number (5
digits) - there can be
up
to
50 rows with the same reference number.

Column D is either 'Adult' or 'Veal'.

Column F is either 'Yes' or 'No' to say if the
animal is satisfactory.

What I need is the number of 'Adult' and 'Veal'
claims that are
satisfactory. A satisfactory claim is defined as
one where all animals
in
a
claim are 'Yes' in column F.

Thanks in advance.

Gareth








.