Help with code or formula

[Gareth]

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

 

[Don Guillett]

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

 

[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

 

[Jez]

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

Jez

 

[Jez]

Yeah just send me an example

-----Original Message-----
example data...

Gareth

 

[Don Guillett]

Gareth,

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

 

[Jez]

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

 

[Gareth]

never used pivot tables, can you help?

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

 

[Dave Peterson]

Here are some links for pivottable info:

Debra Dalgleish's pictures at Jon Peltier's site:
http://www.geocities.com/jonpeltier/Excel/Pivots/pivottables.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/assistance/2002/articles/xlconPT101.aspx