Sum of Two Attribute Greater than X

[MoldyBread]

Data structure: 10 columns (variables), 6000 rows (observation).

Objective 1 was to find for how many rows, value in one columns exceed 0.9. I used:

Please Login or Register  to view this content.

Objective 2: Now I want to find, for how many rows, sum of any 2 variables exceed 0.9. By above method, this would require 90 conditions !. Eventually after this, I also want to do, sum of any 3 variables exceeding 0.9, which well would be 700+ conditions.

Is there a better way of doing this?

[kersplash]

Can you attach a sample workbook?

Go Advanced -> Manage Attachments -> Upload

[MoldyBread]

Sure. Just added one.

[kersplash]

Why don't you use a countif in Column L for anything >0.9 rather than check every cell individually?

[MoldyBread]

Because I also wanted to manually inspect observations falling within criteria. I am not concerned anymore with that tho, I am just wondering if I can check for sum without specifying all the combination inside countifs formula.

[FlameRetired]

The sums criteria requires you 45 combinations to consider in each row.

The attached borrows shamelessly from a solution José Augusto posted last week. They are somewhat similar problems ... especially regarding the helper table.

Find the helper table A:J in sheet 'Lookups' that returns all 769 position combinations for any 10 item row col1+col2 through col9+col10.

Column W of that same sheet

Please Login or Register  to view this content.

returns a row number for combinations of two only. There are 45 of them.

On the main sheet column L

Please Login or Register  to view this content.

counts the number of 2 cell sum combinations > 0.9 in each row.

Then P3 sums those up

Please Login or Register  to view this content.