Count records depending on enties in other cells

[Gareth]

A2:A601 contains the claim number. There may be up to 40
rows with the same claim number, the number starts at 1
and goes up to 15.

B2:B601 tells me what kind of claim it is, either "Adult"
or "Veal". For example if the claim number was 1 then all
records in column B would have either "Adult" or "Veal".

C2:C601 tells me if that record is satisfactory (Yes or
No).

What I want to do in cell D1 is have a total of
satisfactory Veal claims, a satisfactory claim is one
where all records within a claim are Yes.

Thanks in advance.

Gareth

 

[Bernie Deitrick]

Gareth,

You can't do this with a single cell formula.

In cell E1, use the array formula, entered with Ctrl-Shift-Enter:

=IF(SUM(($A$1:$A$601=ROW())*1)<>0,(SUM(($A$1:$A$601=ROW())*($B$1:$B$60
1="Veal")*($C$1:$C$601="No"))=0)*1,"")

Copy this cell down to cells E2:E15.

In Cell D1, use the formula

=SUM(E1:E15)

which will give the total of satisfactory Veal claims.

HTH,
Bernie

 

[Gareth]

Cannot seem to get it to work. I think the fact that
column B contains "Adult" as well as "Veal" is confusing
it.

Gareth

 

[Bernie Deitrick]

Gareth,

The formula doesn't care about other entries, just that "Veal" is
exactly "Veal"

Does it return an error, or simply 0? Have you properly entered it as
an array formula, using Ctrl-Shift-Enter? Did you try it on a smaller
subset of data?

If you still have problems, send me a sample spreadsheet privately -
first three columns only - and I will return you a working version.

HTH,
Bernie

 

[Bernie Deitrick]

Gareth and anyone else interested:

The array formula
=IF(SUM(($A$1:$A$601=ROW())*($B$1:$B$601="Veal")*1)<>0,(SUM(((($A$1:$A$601=R
OW())*($B$1:$B$601="Veal")*1)<>0)*($C$1:$C$601="No"))=0)*1,0)

in cell E1, copied to cells E2:E15, works.

I had ignored the case with all "Adult" in column B and all "Yes" in column
C for an individual claim number.

Bernie