Help with code or formula
 

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

Help with code or formula
 

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

Help with code or formula
 

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

Help with code or formula
 

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








.

Help with code or formula
 

Yeah just send me an example
-----Original Message-----
example data...

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








.

Help with code or formula
 

Gareth,

We try to discourage sending files to the ng. So, when someone says "send me
an example", pls send to their email.

"Gareth" wrote in message
...
example data...

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








.

Help with code or formula
 

It dawned on me last night all you really need to use is
a pivot table, it will amalgamate the data for you.



-----Original Message-----
Gareth,

We try to discourage sending files to the ng. So, when
someone says "send me
an example", pls send to their email.

"Gareth" wrote in message
...
example data...

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








.








.

Help with code or formula
 

never used pivot tables, can you help?

"Jez" wrote in message
...


It dawned on me last night all you really need to use is
a pivot table, it will amalgamate the data for you.



-----Original Message-----
Gareth,

We try to discourage sending files to the ng. So, when
someone says "send me
an example", pls send to their email.

"Gareth" wrote in message
...
example data...

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








.








.

Help with code or formula
 

Here are some links for pivottable info:

Debra Dalgleish's pictures at Jon Peltier's site:
http://www.geocities.com/jonpeltier/...ivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistan...lconPT101.aspx

Gareth wrote:

never used pivot tables, can you help?

"Jez" wrote in message
...


It dawned on me last night all you really need to use is
a pivot table, it will amalgamate the data for you.



-----Original Message-----
Gareth,

We try to discourage sending files to the ng. So, when
someone says "send me
an example", pls send to their email.

"Gareth" wrote in message
...
example data...

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








.








.


--

Dave Peterson