HI, Can someone PLEASE HELP!!!!!!!!!!!!!!!!!

I am using the following count to count how many time a number above 70
is
input, I need to add M or F onto the end of the numbers. e.g. 74M, the
formula i'm using does not recognise both number and text.

this is the formula i'm using: =COUNTIF(Interventions!C19:C81,"70")

I need to add a letter M and F onto the end of this formula to
recognise
input of e.g. 74M, 86M, 95F to place in correct cells on a different
worksheet.

70+ Males for M & 70+ Females for F

I have cells with headings Male 0-5 6-15 16-30 30-70 70+ the same for female
and age ranges these are against medicines.

So e.g. I want to input 56M into a cell I want this to count up into cell
labeled Male 30-70 and so on, keep counting the number of times ranges are
entered to M or F the same if I enter 74F I want this to count in the cell
labelled Female 70+.

Hope you can understand this, thanks for the HELP!!!!!


Can anyone HELP!!!!!!!! Its doing my head in...

Thanks

Hi santaviga,

Try something like:
=SUM(IF((LEFT(Interventions!C19:C81,LEN(Interventi ons!C19:C81)-1)^170)*(RIG
HT(Interventions!C19:C81,1)="M"),1,))
and
=SUM(IF((LEFT(Interventions!C19:C81,LEN(Interventi ons!C19:C81)-1)^170)*(RIG
HT(Interventions!C19:C81,1)="F"),1,))
both entered as array formulae (ie Ctrl-Shift-Enter).

for intermediate age ranges, the formulae could become:
=SUM(IF((LEFT(Interventions!C19:C81,LEN(Interventi ons!C19:C81)-1)^15)*(LEFT
(Interventions!C19:C81,LEN(Interventions!C19:C81)-1)^1<16)*(RIGHT(Interventi
ons!C19:C81,1)="M"),1,))
etc, depending on what's supposed to happen with ages like 5.5, 15.5, and so
on.

Cheers


"santaviga" wrote in message
...
HI, Can someone PLEASE HELP!!!!!!!!!!!!!!!!!

I am using the following count to count how many time a number above
70
is
input, I need to add M or F onto the end of the numbers. e.g. 74M, the
formula i'm using does not recognise both number and text.

this is the formula i'm using: =COUNTIF(Interventions!C19:C81,"70")

I need to add a letter M and F onto the end of this formula to
recognise
input of e.g. 74M, 86M, 95F to place in correct cells on a different
worksheet.

70+ Males for M & 70+ Females for F

I have cells with headings Male 0-5 6-15 16-30 30-70 70+ the same for
female
and age ranges these are against medicines.

So e.g. I want to input 56M into a cell I want this to count up into cell
labeled Male 30-70 and so on, keep counting the number of times ranges are
entered to M or F the same if I enter 74F I want this to count in the cell
labelled Female 70+.

Hope you can understand this, thanks for the HELP!!!!!


Can anyone HELP!!!!!!!! Its doing my head in...

Thanks

This returns a value error..

M

"macropod" wrote:

Hi santaviga,

Try something like:
=SUM(IF((LEFT(Interventions!C19:C81,LEN(Interventi ons!C19:C81)-1)^170)*(RIG
HT(Interventions!C19:C81,1)="M"),1,))
and
=SUM(IF((LEFT(Interventions!C19:C81,LEN(Interventi ons!C19:C81)-1)^170)*(RIG
HT(Interventions!C19:C81,1)="F"),1,))
both entered as array formulae (ie Ctrl-Shift-Enter).

for intermediate age ranges, the formulae could become:
=SUM(IF((LEFT(Interventions!C19:C81,LEN(Interventi ons!C19:C81)-1)^15)*(LEFT
(Interventions!C19:C81,LEN(Interventions!C19:C81)-1)^1<16)*(RIGHT(Interventi
ons!C19:C81,1)="M"),1,))
etc, depending on what's supposed to happen with ages like 5.5, 15.5, and so
on.

Cheers


"santaviga" wrote in message
...
HI, Can someone PLEASE HELP!!!!!!!!!!!!!!!!!

I am using the following count to count how many time a number above
70
is
input, I need to add M or F onto the end of the numbers. e.g. 74M, the
formula i'm using does not recognise both number and text.

this is the formula i'm using: =COUNTIF(Interventions!C19:C81,"70")

I need to add a letter M and F onto the end of this formula to
recognise
input of e.g. 74M, 86M, 95F to place in correct cells on a different
worksheet.

70+ Males for M & 70+ Females for F

I have cells with headings Male 0-5 6-15 16-30 30-70 70+ the same for
female
and age ranges these are against medicines.

So e.g. I want to input 56M into a cell I want this to count up into cell
labeled Male 30-70 and so on, keep counting the number of times ranges are
entered to M or F the same if I enter 74F I want this to count in the cell
labelled Female 70+.

Hope you can understand this, thanks for the HELP!!!!!


Can anyone HELP!!!!!!!! Its doing my head in...

Thanks

Hi santaviga,

That suggests your 'Interventions!C19:C81' range isn't completely filled
with age/sex data. If you have anything other than '#X', where '#' is a
number and 'X' is a single letter, or the cell is empty, the formulae I gave
you in my previous post won't work.

To cope with such conditions, re-code the formulae with 'NOT' & ISERROR'
tests, like:
=SUM(IF(NOT(ISERROR((LEFT(Interventions!C19:C81,LE N(Interventions!C19:C81)-1
)^170)*(RIGHT(Interventions!C19:C81,1)="M"))),1,) )
etc. or with an ISERROR' test, like:
=SUM(IF(ISERROR((LEFT(Interventions!C19:C81,LEN(In terventions!C19:C81)-1)^1
70)*(RIGHT(Interventions!C19:C81,1)="M")),,1))
etc. (note the change in the position of the '1' at the end of the formula).

Cheers


"santaviga" wrote in message
...
This returns a value error..

M

"macropod" wrote:

Hi santaviga,

Try something like:

=SUM(IF((LEFT(Interventions!C19:C81,LEN(Interventi ons!C19:C81)-1)^170)*(RIG
HT(Interventions!C19:C81,1)="M"),1,))
and

=SUM(IF((LEFT(Interventions!C19:C81,LEN(Interventi ons!C19:C81)-1)^170)*(RIG
HT(Interventions!C19:C81,1)="F"),1,))
both entered as array formulae (ie Ctrl-Shift-Enter).

for intermediate age ranges, the formulae could become:

=SUM(IF((LEFT(Interventions!C19:C81,LEN(Interventi ons!C19:C81)-1)^15)*(LEFT

(Interventions!C19:C81,LEN(Interventions!C19:C81)-1)^1<16)*(RIGHT(Interventi
ons!C19:C81,1)="M"),1,))
etc, depending on what's supposed to happen with ages like 5.5, 15.5,
and so
on.

Cheers


"santaviga" wrote in message
...
HI, Can someone PLEASE HELP!!!!!!!!!!!!!!!!!

I am using the following count to count how many time a number
above
70
is
input, I need to add M or F onto the end of the numbers. e.g. 74M,
the
formula i'm using does not recognise both number and text.

this is the formula i'm using:
=COUNTIF(Interventions!C19:C81,"70")

I need to add a letter M and F onto the end of this formula to
recognise
input of e.g. 74M, 86M, 95F to place in correct cells on a
different
worksheet.

70+ Males for M & 70+ Females for F

I have cells with headings Male 0-5 6-15 16-30 30-70 70+ the same for
female
and age ranges these are against medicines.

So e.g. I want to input 56M into a cell I want this to count up into
cell
labeled Male 30-70 and so on, keep counting the number of times ranges
are
entered to M or F the same if I enter 74F I want this to count in the
cell
labelled Female 70+.

Hope you can understand this, thanks for the HELP!!!!!


Can anyone HELP!!!!!!!! Its doing my head in...

Thanks

To model your problem I did the following:

I put the word "Input" in A1, and defined a named range called "Data"
covering A2 to A20 - obviously you might need to extend this range,
depending how much data you have.

I put this test data in A2 to A9:

21F
56M
74F
3M
33F
47M
65F
66F

I put these values in cells F1 to K1:

0, 6, 16, 31, 71, 200, and these headings in F2 to J2:

0-5 6-15 16-30 31-70 71+

I put the word "Male" in E3 and "Female" in E4, and in F3 I entered
this array formula*:

=SUM(IF(Data="",0,IF((RIGHT(Data,1)=LEFT($E3,1))*( VALUE(LEFT(Data,LEN(Data)-1))F$1)*(VALUE(LEFT(Data,LEN(Data)-1))<G$1),1,0)))

* As this is an array formula, once you have typed it in (or
subsequently edit it), you must use CTRL-SHIFT-ENTER rather than just
ENTER. If you do this correctly then Excel will wrap curly braces { }
around the formula - you must not type these yourself.

The formula can then be copied to G3:J3 and to F4:J4. The result is
this table in E2:J4

0-5 6-15 16-30 31-70 71+
Male 1 0 0 2 0
Female 0 0 1 3 1

You should be able to change the references to suit your own
circumstances.

Hope this helps.

Pete

Hi Pete_UK,

Thanks for this it works a treat but cant figure out how to put it into
practise on my cells, I need to take the data from another sheet and put it
onto a summary sheet, can this formula include data from another sheet, also
instead of reference cells can these be replaced by text??

Thanks for your help!!!

Mark

"Pete_UK" wrote:

To model your problem I did the following:

I put the word "Input" in A1, and defined a named range called "Data"
covering A2 to A20 - obviously you might need to extend this range,
depending how much data you have.

I put this test data in A2 to A9:

21F
56M
74F
3M
33F
47M
65F
66F

I put these values in cells F1 to K1:

0, 6, 16, 31, 71, 200, and these headings in F2 to J2:

0-5 6-15 16-30 31-70 71+

I put the word "Male" in E3 and "Female" in E4, and in F3 I entered
this array formula*:

=SUM(IF(Data="",0,IF((RIGHT(Data,1)=LEFT($E3,1))*( VALUE(LEFT(Data,LEN(Data)-1))F$1)*(VALUE(LEFT(Data,LEN(Data)-1))<G$1),1,0)))

* As this is an array formula, once you have typed it in (or
subsequently edit it), you must use CTRL-SHIFT-ENTER rather than just
ENTER. If you do this correctly then Excel will wrap curly braces { }
around the formula - you must not type these yourself.

The formula can then be copied to G3:J3 and to F4:J4. The result is
this table in E2:J4

0-5 6-15 16-30 31-70 71+
Male 1 0 0 2 0
Female 0 0 1 3 1

You should be able to change the references to suit your own
circumstances.

Hope this helps.

Pete