Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

1. I agreed to these rules
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

john

Excel Video Tutorials / Excel Dashboards Reports

2. Long Term Member
Join Date
13th May 2006
Location
India
Posts
1,030

## 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

Excel Video Tutorials / Excel Dashboards Reports

3. I agreed to these rules
Join Date
14th March 2012
Posts
4

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

Hi

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. Long Term Member
Join Date
13th May 2006
Location
India
Posts
1,030

## 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. I agreed to these rules
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. Long Term Member
Join Date
13th May 2006
Location
India
Posts
1,030

## 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

Excel Video Tutorials / Excel Dashboards Reports

7. I agreed to these rules
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

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

#### 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