Hi,
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
Sheet1:
| 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?
