+ Reply to Thread
Results 1 to 4 of 4

condition formating I think

  1. #1
    Dreamstar_1961
    Guest

    condition formating I think

    I have a number of cells set up with numbers set in them,

    Exp
    A B C D E
    1 1 3 8 16 33
    2 16 22 23 33 41
    3 9 11 30 39 41
    4 14 18 19 22 42
    5 15 19 22 32 33
    6 7 8 18 26 36

    the next part is some other numbers
    exp- 1 14 15 19 42
    this is setup up to change the colour of the cell, what i want is to be
    able to have some cells down below that will count how many cells in each row
    match,
    as in row 1=1 row 4=3 row 5=1 and so on, any help if it possiable , would be
    of a great help, thanks in advanced for any help



  2. #2
    galimi
    Guest

    RE: condition formating I think

    You can use a formula array similar to the following:

    =SUM(IF(A11=A13:E13,1,0))+SUM(IF(B11=A13:E13,1,0))

    Where A11 is the first number in the first row of numbers, a13:e13 is the
    number set you are comparing against, b11 is the second number, and you will
    have to add 3 other sets. Be sure to enter this as a formula array by
    pressing ctrl-shift-enter
    --
    http://HelpExcel.com
    1-888-INGENIO
    1-888-464-3646
    x0197758


    "Dreamstar_1961" wrote:

    > I have a number of cells set up with numbers set in them,
    >
    > Exp
    > A B C D E
    > 1 1 3 8 16 33
    > 2 16 22 23 33 41
    > 3 9 11 30 39 41
    > 4 14 18 19 22 42
    > 5 15 19 22 32 33
    > 6 7 8 18 26 36
    >
    > the next part is some other numbers
    > exp- 1 14 15 19 42
    > this is setup up to change the colour of the cell, what i want is to be
    > able to have some cells down below that will count how many cells in each row
    > match,
    > as in row 1=1 row 4=3 row 5=1 and so on, any help if it possiable , would be
    > of a great help, thanks in advanced for any help
    >
    >


  3. #3
    Dreamstar_1961
    Guest

    RE: condition formating I think

    tryed that but it is not counting up , is returnin one only, I got it to work
    by using a lot of count if, , this is how I did it but dont think its right
    =COUNTIF(B4:F4,D1)+COUNTIF(B4:F4,E1)+COUNTIF(B4:F4,F1)+COUNTIF(B4:F4,G1)+COUNTIF(B4:F4,H1)
    that is the first row of the cells

    "galimi" wrote:

    > You can use a formula array similar to the following:
    >
    > =SUM(IF(A11=A13:E13,1,0))+SUM(IF(B11=A13:E13,1,0))
    >
    > Where A11 is the first number in the first row of numbers, a13:e13 is the
    > number set you are comparing against, b11 is the second number, and you will
    > have to add 3 other sets. Be sure to enter this as a formula array by
    > pressing ctrl-shift-enter
    > --
    > http://HelpExcel.com
    > 1-888-INGENIO
    > 1-888-464-3646
    > x0197758
    >
    >
    > "Dreamstar_1961" wrote:
    >
    > > I have a number of cells set up with numbers set in them,
    > >
    > > Exp
    > > A B C D E
    > > 1 1 3 8 16 33
    > > 2 16 22 23 33 41
    > > 3 9 11 30 39 41
    > > 4 14 18 19 22 42
    > > 5 15 19 22 32 33
    > > 6 7 8 18 26 36
    > >
    > > the next part is some other numbers
    > > exp- 1 14 15 19 42
    > > this is setup up to change the colour of the cell, what i want is to be
    > > able to have some cells down below that will count how many cells in each row
    > > match,
    > > as in row 1=1 row 4=3 row 5=1 and so on, any help if it possiable , would be
    > > of a great help, thanks in advanced for any help
    > >
    > >


  4. #4
    galimi
    Guest

    RE: condition formating I think

    Ensure that you are pressing cntrl-shift-enter after typing the formula.
    This will enter it as a formula array.
    --
    http://HelpExcel.com
    1-888-INGENIO
    1-888-464-3646
    x0197758


    "Dreamstar_1961" wrote:

    > tryed that but it is not counting up , is returnin one only, I got it to work
    > by using a lot of count if, , this is how I did it but dont think its right
    > =COUNTIF(B4:F4,D1)+COUNTIF(B4:F4,E1)+COUNTIF(B4:F4,F1)+COUNTIF(B4:F4,G1)+COUNTIF(B4:F4,H1)
    > that is the first row of the cells
    >
    > "galimi" wrote:
    >
    > > You can use a formula array similar to the following:
    > >
    > > =SUM(IF(A11=A13:E13,1,0))+SUM(IF(B11=A13:E13,1,0))
    > >
    > > Where A11 is the first number in the first row of numbers, a13:e13 is the
    > > number set you are comparing against, b11 is the second number, and you will
    > > have to add 3 other sets. Be sure to enter this as a formula array by
    > > pressing ctrl-shift-enter
    > > --
    > > http://HelpExcel.com
    > > 1-888-INGENIO
    > > 1-888-464-3646
    > > x0197758
    > >
    > >
    > > "Dreamstar_1961" wrote:
    > >
    > > > I have a number of cells set up with numbers set in them,
    > > >
    > > > Exp
    > > > A B C D E
    > > > 1 1 3 8 16 33
    > > > 2 16 22 23 33 41
    > > > 3 9 11 30 39 41
    > > > 4 14 18 19 22 42
    > > > 5 15 19 22 32 33
    > > > 6 7 8 18 26 36
    > > >
    > > > the next part is some other numbers
    > > > exp- 1 14 15 19 42
    > > > this is setup up to change the colour of the cell, what i want is to be
    > > > able to have some cells down below that will count how many cells in each row
    > > > match,
    > > > as in row 1=1 row 4=3 row 5=1 and so on, any help if it possiable , would be
    > > > of a great help, thanks in advanced for any help
    > > >
    > > >


+ 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