Count records depending on enties in other cells

  • Thread starter Thread starter Gareth
  • Start date Start date
G

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
 
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
 
Cannot seem to get it to work. I think the fact that
column B contains "Adult" as well as "Veal" is confusing
it.

Gareth
 
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
 
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
 
Back
Top