# Same Formula in two different cells returning different answers

I am using this formula:

`=IF(MASTER!B68:AL68="Rezdy",SUM(MASTER!B77:AL77),0)`

It returns 400.

But if I paste it exactly the same in another cell, I get the return 0.

Any ideas how to solve? • @Patrick perhaps share a sample Google Sheet that you have those two formulas in Jul 20 at 17:35
• Added a screenshot. Good tip Yisroel Jul 21 at 7:08
• The screeshot does not show MASTER data. Consider sharing a publicly editable sample spreadsheet with realistic-looking data, and showing your manually entered desired results there. Jul 21 at 7:35

When you copy a formula cell and paste into another cell, range references in the formula get adjusted relative to the locations of the two formula cells. To make range references in the formula remain the same, use absolute references with `\$` prefixes. See absolute and relative references.

The range references in your formula are not pointing to cells but to arrays of cells. When such references are used in scalar functions such as `eq()` or `=`, exactly one cell is picked from the array based on the relative location of the formula cell. To avoid that, refer to a single cell, and use absolute addressing when you do not want the cell reference get adjusted based on formula location.

Aggregating functions such as `sum()` always refer to the whole array. Whether relative or absolute addressing should be used depends on the semantics of what you are trying to do.

Try something like this to get started:

`=if(MASTER!B68 = "Rezdy", sum(MASTER!\$B\$77:\$AL\$77), iferror(1/0) )`

• Thanks double. I checked that, but the formula are exactly the same. Jul 21 at 7:08
• That may be the case, but as Ruben pointed out, the range references in the formula are not pointing to cells but to arrays of cells, which affects semantics with scalar functions. Edited the answer. Jul 21 at 7:31
• Thank you, I will. Lots the learn and I appreciate the help. Jul 22 at 11:39
• I gather that this is your first question at Stack Overflow. See What should I do when someone answers my question? Jul 22 at 19:44

The IF function is comparing an array (MASTER!B68:AL68) to a value ("Rezdy"). This makes that the IF function returns multiple values, but by default it only will show a single value based on the position of the formula.

Depending of what you are looking for, you might have to modify `MASTER!B68:AL68="Rezdy"`, use the ArrayFormula function to get all the values returned by the IF function, or to modify the whole formula.

• Thanks Ruben. Im still learning Goggle Sheets. Ive added a screenshot. Basically, Im trying to get totals for different sales channels in a day. The formula worked perfectly with 'Rezdy' but when I changed that to 'Shop' it returned zero (even tho there are sales). After checking everything I decided to duplicate the original formula in the neighbouring cell and it still returned 0. Hence the post Jul 21 at 7:12
• That is not the question you are asking in your post. See What is the XY problem? Jul 21 at 7:40
• @Patrick While the screenshots give additional details (formula positions) they aren't enough make a more specific answer.
– Rubén
Jul 21 at 13:07