Hi santaviga,

With these headings in F2 to J2:
0-5 6-15 16-30 31-70 71-199
plus the word "Male" in E3 and "Female" in E4, enter this array formula in
F3:
=SUM(IF(ISERROR((RIGHT(Data)<"F")*(RIGHT(Data)<" M")*LEFT(Data,LEN(Data)-1)
^1),0,IF((LEFT(Data,LEN(Data)-1)^1=LEFT(F$2,FIND("-",F$2)-1)^1)*(LEFT(Data,
LEN(Data)-1)^1<=RIGHT(F$2,LEN(F$2)-FIND("-",F$2))^1)*(RIGHT(Data,1)=LEFT($E3
,1)),1,)))
and copy across/down to J4.

That should be enough to trap any likely error condition.

Cheers
PS: This formula doesn't require Pete's F1-J1 range, and you can change the
'199' to any other suitable maximum age. The formula also allows you to
easily add more age ranges or change them


"santaviga" wrote in message
...
Hi Pete,

Still having problems, just noticed it when I need to input more data into
cells above and below e.g 74M. the way the cells are set out is as
follows:
Cell Heading - Epi

PRF No: 1234567
Date: 01/05/2006
Age/gender: 74M
Case Type: Collapse
Outcome: POS
Dose 1mg

This is how the layout is in the cells on interventions, so when I input
this data I get an error ?value as the cells contain other than 74M or
56F,
is there a way I can edit the formula so that it counts only the cells
that
contain 56M and not come up with an error for inputting the other data,
there
are approx 50 cells that will contain e.g. 74M.

Sorry to be a pain.

Thanks again for the help. much appreciated.


Mark


"Pete_UK" wrote:

Mark,

Thanks for feeding back.

COUNTIF only works on a single condition and you have 3 - Male/Female,
lower age and upper age. You could probably have used SUMPRODUCT, but
I'm more familiar with array formulae.

Glad to be of help.

Pete