Excel sort messing up formulas


I've encountered a seemingly very odd bug and could use some guidance. The following is a very simple demonstration of the issue I'm facing:

I have two sheets, one with a list of items and a count of the sales, the other with a list of items sold


Item Quantity sold (formula used) Quantity sold (Result)
f =COUNTIF(Sheet2!A:A,Sheet1!A2) 1
a =COUNTIF(Sheet2!A:A,Sheet1!A3) 4
b =COUNTIF(Sheet2!A:A,Sheet1!A4) 2
e =COUNTIF(Sheet2!A:A,Sheet1!A5) 3
c =COUNTIF(Sheet2!A:A,Sheet1!A6) 1
d =COUNTIF(Sheet2!A:A,Sheet1!A7) 2

Sheet 2

is just the following list in cells a2:a14: a, a, b, b, c, c, d,e, e, e, a, a, f

As you can see, all is good and the result produced are as expected.

However if I now sort the first sheet by item, something odd happens. The formula's are moved to have no bearing on the cell they are next to and therefore producing the wrong result:

Item Quantity sold (formula used) Quantity sold (Result)
a =COUNTIF(Sheet2!A:A,Sheet1!A3) 1
b =COUNTIF(Sheet2!A:A,Sheet1!A4) 2
c =COUNTIF(Sheet2!A:A,Sheet1!A7) 1
d =COUNTIF(Sheet2!A:A,Sheet1!A6) 3
e =COUNTIF(Sheet2!A:A,Sheet1!A5) 1
f =COUNTIF(Sheet2!A:A,Sheet1!A2) 4

There seems to be no logic to this - they are not pointing to where the value that used to be in that line has now ended up, nor where it came from. It is seemingly random, and therefore the wrong result of the count is next to each of a-f.

I realise that a potential workaround for this is that you copy and past values before sorting, but I need to formula to keep working, as the data behind will change. 

Bizarrely I have also found the fix, but it requires you to counteract default excel behaviour. If you change the formula to be  =COUNTIF(Sheet2!A:A,A2) (i.e. taking out the reference to sheet1, it is fixed.

1. I still don't understand why the issue is arising in the first place.

2. Excel automatically puts in the reference to sheet1 (assuming that to construct the formula you switch to sheet 2 to select column A, the  switch back to sheet1 to select cell A2), so you manually have to delete the reference... this is somewhat annoying behaviour.

Does anyone have any insights to this?


The problem is, as you say, a result of including the Sheet1! in the formula.

Sort sees the sheet reference and decides, wrongly, that it isn't a reference to a cell on the same row within the data that is being sorted, so it treats it like a reference to a cell outside the area being sorted and moves the formula unchanged along with the item to its left.

I agree that this is annoying and it annoys me too that the sheet name is automatically included when you have visited another sheet earlier in the formula and then return to the sheet on which you are entering the formula.

The only solution I know is to manually remove the Sheet1! from the first formula before copying it down to the rest of the table.

Microsoft Excel MVP, Oxford, England. www.manville.org.uk

210 people were helped by this reply


Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.


Question Info

Last updated July 6, 2020 Views 38,312 Applies to: