# Thread: Sum values in range based on top values in another range with criteria in third range

1. ## Sum values in range based on top values in another range with criteria in third range

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

2. 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

3. 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.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•