Sum values in range based on top values in another range with criteria in third range

samdk

New member
Joined
Dec 14, 2018
Messages
13
Reaction score
0
Points
1
Location
Paris
Excel Version(s)
O365
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):
Year 1Year 2VariationCriteria
1001055A
9585-10A
9590-5A
859510B

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 think I found a solution but it involves a helper cells

1. This formula would return the smallest number in Variance range:
=SMALL(IF(range "Criteria"="A"; range "Variation");1)

for the second smallest the formula is:

=SMALL(IF(range "Criteria"="A"; range "Variation");2) etc.

Once you know which are the nth smallest numbers it is easy to sum the numbers for respective year using SUMIFS
 
Thanks a lot Kolyu for your time and reply.

The formula works, but unfortunately can't be adapted to my real case care the Top nth value is variable.
 
Back
Top