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

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

Is it possible to freeze the Cell index during sort?

回答1:

Try a SUMIF function with OR criteria.

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

回答2:

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.