CODEWITHSUNDEEP
excelexcel-formulaexcel-2010
giboo
giboo

Reputation: 103

Sort without altering cell index

I am looking for a solution to following issue :

I want to know the sum of A/Cs 3, 4 and 5 all the time in H7 (In Picture). But, I will also want to sort the date occasionally to verify the next due but without affecting H7

Before sort

But, What actually happens when sorting in ascending order, the Cell index itself is getting changed and the value at H7 gives me the different value (which is not what I want).

After Sort

Is it possible to freeze the Cell index during sort?

Upvotes: 1

Views: 61

Answers (2)

user4039065
user4039065

Reputation:

Try a SUMIF function with OR criteria.

=SUM(SUMIF(A:A, {3,4,5}, B:B ))

Upvotes: 1

Zeeshan S.
Zeeshan S.

Reputation: 2091

Use the formula to calculate total: =SUMIFS(B4:B8,A4:A8,">=3",A4:A8,"<=5")

What this does is it adds the values in the range B4:B8 only when values in range A4:A8 is greater than or equal to 3 and less than or equal to 5.

Upvotes: 2

Related Questions