count a number range and a letter in a cell
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 |
count a number range and a letter in a cell
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 "santaviga" wrote: 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 |
count a number range and a letter in a cell
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... "Ron Coderre" wrote: 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 "santaviga" wrote: 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 |
count a number range and a letter in a cell
Hi Ron,
This is returning a Value... M "Ron Coderre" wrote: 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 "santaviga" wrote: 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 |
All times are GMT +1. The time now is 06:48 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com