+ Reply to Thread
Results 1 to 4 of 4

count a number range and a letter in a cell

  1. #1
    santaviga
    Guest

    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 ***. e.g 76M, 50F

  2. #2
    Ron Coderre
    Guest

    RE: 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 ***. e.g 76M, 50F


  3. #3
    santaviga
    Guest

    RE: 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 ***. e.g 76M, 50F


  4. #4
    santaviga
    Guest

    RE: 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 ***. e.g 76M, 50F


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1