software

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

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)=quot;Fquot;)*(--(LEFT(quot;0quot;amp;Sheet1!A1:A10,3))gt;=39)*(--(LEFT(quot;0quot;amp;Sheet1!A1:A10,3))lt;=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 quot;Fquot;

Is that something you can work with?

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

XL2002, WinXP-Proquot;santavigaquot; wrote:

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

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,quot;gt;70quot;), 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...

quot;Ron Coderrequot; wrote:

gt; Perhaps something like this:
gt;
gt; With values in A1:A10 on Sheet1
gt; where some cells may be blank
gt;
gt; On Sheet2
gt; A1:
gt; =SUMPRODUCT((RIGHT(Sheet1!A1:A10,1)=quot;Fquot;)*(--(LEFT(quot;0quot;amp;Sheet1!A1:A10,3))gt;=39)*(--(LEFT(quot;0quot;amp;Sheet1!A1:A10,3))lt;=60))
gt;
gt; Note: in case of text wrapping, there are no spaces in that formula
gt;
gt; That formula counts the number entries between 39-60 that end in quot;Fquot;
gt;
gt; Is that something you can work with?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;santavigaquot; wrote:
gt;
gt; gt; Anyone help? I need to have a running total of numbers and text in the same
gt; gt; cell onto another sheet and cell. e.g 74M, 80M will total in a cell Male 70 .
gt; gt; 56F, 40F will total in a cell Female 39-60, any ideas for a formula for this,
gt; gt; I can get the cell to recognise the ages, also recognise whether text is M or
gt; gt; F but cant get the cell to recognise both as text 74M. info in cell is input
gt; gt; as AGE then sex. e.g 76M, 50F

Hi Ron,

This is returning a Value...M

quot;Ron Coderrequot; wrote:

gt; Perhaps something like this:
gt;
gt; With values in A1:A10 on Sheet1
gt; where some cells may be blank
gt;
gt; On Sheet2
gt; A1:
gt; =SUMPRODUCT((RIGHT(Sheet1!A1:A10,1)=quot;Fquot;)*(--(LEFT(quot;0quot;amp;Sheet1!A1:A10,3))gt;=39)*(--(LEFT(quot;0quot;amp;Sheet1!A1:A10,3))lt;=60))
gt;
gt; Note: in case of text wrapping, there are no spaces in that formula
gt;
gt; That formula counts the number entries between 39-60 that end in quot;Fquot;
gt;
gt; Is that something you can work with?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;santavigaquot; wrote:
gt;
gt; gt; Anyone help? I need to have a running total of numbers and text in the same
gt; gt; cell onto another sheet and cell. e.g 74M, 80M will total in a cell Male 70 .
gt; gt; 56F, 40F will total in a cell Female 39-60, any ideas for a formula for this,
gt; gt; I can get the cell to recognise the ages, also recognise whether text is M or
gt; gt; F but cant get the cell to recognise both as text 74M. info in cell is input
gt; gt; as AGE then sex. e.g 76M, 50F