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?
-
Welcome to Web Applications Stack Exchange. See How do I ask a good question?– doubleunaryCommented Jul 20, 2022 at 15:06
-
@Patrick perhaps share a sample Google Sheet that you have those two formulas in– Yisroel TechCommented Jul 20, 2022 at 17:35
-
Added a screenshot. Good tip Yisroel– PatrickCommented Jul 21, 2022 at 7:08
-
2The 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.– doubleunaryCommented Jul 21, 2022 at 7:35
Add a comment
|
2 Answers
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) )
answered Jul 20, 2022 at 15:05
-
Thanks double. I checked that, but the formula are exactly the same.– PatrickCommented Jul 21, 2022 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. Commented Jul 21, 2022 at 7:31
-
Thank you, I will. Lots the learn and I appreciate the help.– PatrickCommented Jul 22, 2022 at 11:39
-
I gather that this is your first question at Stack Overflow. See What should I do when someone answers my question? Commented Jul 22, 2022 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.
answered Jul 20, 2022 at 15:59
Rubén - Volunteer Moderator -♦Rubén - Volunteer Moderator -
46.1k18 gold badges100 silver badges297 bronze badges
-
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– PatrickCommented Jul 21, 2022 at 7:12
-
That is not the question you are asking in your post. See What is the XY problem? Commented Jul 21, 2022 at 7:40
-
@Patrick While the screenshots give additional details (formula positions) they aren't enough make a more specific answer. Commented Jul 21, 2022 at 13:07