count a number range and a letter in a cell

S

santaviga

Anyone help? I need to have a running total of numbers and text in the same
cell onto another sheet and cell. e.g 74M, 80M will total in a cell Male 70+.
56F, 40F will total in a cell Female 39-60, any ideas for a formula for this,
I can get the cell to recognise the ages, also recognise whether text is M or
F but cant get the cell to recognise both as text 74M. info in cell is input
as AGE then sex. e.g 76M, 50F
 
R

Ron Coderre

Perhaps something like this:

With values in A1:A10 on Sheet1
where some cells may be blank

On Sheet2
A1:
=SUMPRODUCT((RIGHT(Sheet1!A1:A10,1)="F")*(--(LEFT("0"&Sheet1!A1:A10,3))>=39)*(--(LEFT("0"&Sheet1!A1:A10,3))<=60))

Note: in case of text wrapping, there are no spaces in that formula

That formula counts the number entries between 39-60 that end in "F"

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP-Pro
 
S

santaviga

Not working... I have it set up just now as COUNTIF, this works between age
ranges and puts the total into a cell that I wish, but will not work if I add
number and a letter. e.g of formula I have is
=COUNTIF(Interventions!C19:C80,">70"), this counts the number of entries over
number 70, I need this calculation to also recognise if theres an M or F
after this calculation. Input as 74M and counts in a cell marked MALE AGE
+...

Thanks for the help...
 
S

santaviga

Hi Ron,

This is returning a Value...


M

Ron Coderre said:
Perhaps something like this:

With values in A1:A10 on Sheet1
where some cells may be blank

On Sheet2
A1:
=SUMPRODUCT((RIGHT(Sheet1!A1:A10,1)="F")*(--(LEFT("0"&Sheet1!A1:A10,3))>=39)*(--(LEFT("0"&Sheet1!A1:A10,3))<=60))

Note: in case of text wrapping, there are no spaces in that formula

That formula counts the number entries between 39-60 that end in "F"

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP-Pro
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top