0

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?enter image description here

4

2 Answers 2

1

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

4
  • Thanks double. I checked that, but the formula are exactly the same.
    – Patrick
    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.
    – Patrick
    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
0

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.

3
  • 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
    – Patrick
    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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.