Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 7 of 7

Thread: Adding up rows, when specific criteria are met

  1. #1
    Join Date
    14th March 2012
    Posts
    4

    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    13th May 2006
    Location
    India
    Posts
    1,007

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    14th March 2012
    Posts
    4

    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

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    13th May 2006
    Location
    India
    Posts
    1,007

    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

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    14th March 2012
    Posts
    4

    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

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    13th May 2006
    Location
    India
    Posts
    1,007

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    14th March 2012
    Posts
    4

    Re: Adding up rows, when specific criteria are met

    Excellent stuff.

    Thank you very much for your help

    John

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Adding Rows and Formulas Based on Criteria
    By Angry Gerbil in forum EXCEL HELP
    Replies: 6
    Last Post: June 21st, 2011, 14:40
  2. Replies: 2
    Last Post: March 1st, 2007, 19:40
  3. Replies: 9
    Last Post: May 24th, 2006, 07:38
  4. Adding rows of information when criteria is met
    By billyj in forum EXCEL HELP
    Replies: 3
    Last Post: September 17th, 2005, 04:26
  5. Adding specific rows & other Qs
    By mickey790 in forum EXCEL HELP
    Replies: 1
    Last Post: March 5th, 2005, 19:38

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
  •  
porno