Announcement

Collapse
No announcement yet.

Adding up rows, when specific criteria are met

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Adding up rows, when specific criteria are met

    Hi

    I have set a spreadsheet up containing data relating to individuals. Each row is a different person.

    In the first four columns I have numerical data which relates to a persons score, ie 0.100, 0.250 etc

    In the fifth column I have text, which relates to an outcome for a person.

    A B C D E
    0.100 0.250 0.125 0.150 Winner
    0.050 0.075 0.100 0.050 Loser

    So I have around 300 rows, = 300 people.

    So first off what I want to do is to add up the no. of people whose scores were all under 0.100. So I only want it to count that row / person if all their scores are under 0,100 in the forst four columns.

    I tried using countifs function but it keeps giving me an error.

    The next thing I would want to do is to count up the number of people who had all there scores under 0.100 but were also winners.

    How do I go about doing this

    Thanks in advance

    john

  • #2
    Re: Adding up rows, when specific criteria are met

    see attachment "john.xls"

    see the formulas F2 and H2 which are copied down
    also see formulas in G2 and I2
    Attached Files
    I am not an expert. better solutions may be available. [email protected]$$$gmail.com

    Comment


    • #3
      Re: Adding up rows, when specific criteria are met

      Hi

      Thanks for the reply.
      With the countif function you used on that spreadsheet, in column G, it doesnt work correctly, as it is counting the rows in the range if only one score is under 0.100.

      I only want it to count that person / row if all there scores are under 0.100

      Thanks

      John

      Comment


      • #4
        Re: Adding up rows, when specific criteria are met

        change the formula in G2
        =COUNTIF($H$2:$H$100,"<.1")
        this means count rows whose even maximum in that row is less than(not even equal to) less than .1

        in your case it is 0
        because even your second person is having one .1 thoputhothers are less.

        is this ok

        the trick is to get max and min in each row and manipulate
        I am not an expert. better solutions may be available. [email protected]$$$gmail.com

        Comment


        • #5
          Re: Adding up rows, when specific criteria are met

          Excellant, working fine now, thank you.

          If I were to work out the standard deviation of each persons (row) data against some "norm" data and put that in a column next to the four scores,

          Would I be able to tell excel to work out the overall stadard deviation for the persons that had all there scores less than 0.100.

          So basically if all four scores are under 0.100, please take the standard deviation from that person, group them all together and then workout a mean SD.

          Thanks

          John

          Comment


          • #6
            Re: Adding up rows, when specific criteria are met

            john.xls is again attached

            see the formula in column J
            this gives std dev of data in those rows whose value <0.1

            to get std dev for all the data in all the rows whose all values are less than 0.1 I have a macro "test" in the module
            the result of the macro is in K2
            Attached Files
            I am not an expert. better solutions may be available. [email protected]$$$gmail.com

            Comment


            • #7
              Re: Adding up rows, when specific criteria are met

              Excellent stuff.

              Thank you very much for your help

              John

              Comment

              Working...
              X