samdk
New member
Hello everyone,
I've been struggling for a while to find the solution for my problem, hope someone will help out.
So, I have this table (will more than 2000 rows):
Firstly I needed to find out the sum of Top nth negative values (in this case top 2) from the Variation column with A as Criteria.
which is -15
=sumproduct(small((range "Criteria" = A)*(range "Variation",row(indirect("1:2")))
I also need to find out what was the sums from Year 1 and 2 based on the Top 2 negative values from the Variation column, keeping the criteria A.
The answer would be : Year 1 => 95 +95 = 190; Year 2=> 85+90 =175.
Thanks in advance for your time and knowledge !
Cheers!
I've been struggling for a while to find the solution for my problem, hope someone will help out.
So, I have this table (will more than 2000 rows):
Year 1 | Year 2 | Variation | Criteria |
100 | 105 | 5 | A |
95 | 85 | -10 | A |
95 | 90 | -5 | A |
85 | 95 | 10 | B |
Firstly I needed to find out the sum of Top nth negative values (in this case top 2) from the Variation column with A as Criteria.
which is -15
=sumproduct(small((range "Criteria" = A)*(range "Variation",row(indirect("1:2")))
I also need to find out what was the sums from Year 1 and 2 based on the Top 2 negative values from the Variation column, keeping the criteria A.
The answer would be : Year 1 => 95 +95 = 190; Year 2=> 85+90 =175.
Thanks in advance for your time and knowledge !
Cheers!