Moving formulae argument during sorting

[Villalobos]

Hi,

I have a general question, maybe a little bit beginner... so please, be patient with me... :/

I have to sort the data based on two cells and during the sorting the criterium argument of SUMIF formulae is not moving with the right cells. Each time after that I pasted and sorted the new data source I have to correct manually the formulae (Sheet1 - column Delivered) in the first cell and after copy down this to see the right result.

How can you avoid having to manually correct the formula after the new data source has been sorted?

I uploaded the sample file to see step by step the problem.



Thanks in advance the reply!

 

[Villalobos]

@Forum Moderator!
Accidentally I wrote wrong title to this thread. Sorry for that.


The right title of thread: Moving formulae argument during sorting.
Please, if needed correct it.

 

[NARAYANK991]

Hi ,

If you sort only the 4 columns of data , instead of including column E also in the SORT , there is no problem.

Narayan

 

[NARAYANK991]

Hi ,

The problem is also resolved if you change your formula to :

=IF(COUNTIF($B$5:$B5,$B5)<>1,0,SUMIF(Sheet2!$C$2:$C$4,$B5,Sheet2!$D$2:$D$4))

omitting the sheet reference in Sheet1!$B5.

Narayan

 

[Villalobos]

Hello Narayan,

Thank you the quick tip!