Skip to main content
Solved

Adding together numeric values based on If Formula result

J
Forum|alt.badge.img+4

Hopefully one of you amazing pros can help me resolve an issue with a nested IF Formula that isn’t quite doing what I hoped it would.

I have four individual products (Line Item #1 through to Line Item #4) and if they are ‘Espresso Blend’ I’d then like their total weight {Total Coffee Weight (G)} to be added together to give me the total amount of Espresso Blend across all of the products combined.

For reasons I can’t quite understand the below formula isn’t very happy when the Line Item isn’t Espresso Blend. Rather than throwing the desired ‘0’ into the calculation it is cancelling out everything but the first item to include Espresso Blend.

Any help would be greatly appreciated!

IF({Line Item #1 Coffee}=“Espresso Blend”, {Total Coffee Weight (G): Line Item #1 Quantity},0
+
IF({Line Item #2 Coffee}=“Espresso Blend”, {Total Coffee Weight (G): Line Item #2 Quantity},0
+
IF({Line Item #3 Coffee}=“Espresso Blend”, {Total Coffee Weight (G): Line Item #3 Quantity},0
+
IF({Line Item #4 Coffee}=“Espresso Blend”, {Total Coffee Weight (G): Line Item #4 Quantity},0

))))

Best answer by Chris-T

I think you need to sum the IFs as individual statements, not nested.

IF({Line Item #1 Coffee}=“Espresso Blend”, {Total Coffee Weight (G): Line Item #1 Quantity},0)
+
IF({Line Item #2 Coffee}=“Espresso Blend”, {Total Coffee Weight (G): Line Item #2 Quantity},0)
+
IF({Line Item #3 Coffee}=“Espresso Blend”, {Total Coffee Weight (G): Line Item #3 Quantity},0)
+
IF({Line Item #4 Coffee}=“Espresso Blend”, {Total Coffee Weight (G): Line Item #4 Quantity},0)

Of course, look out for your double quotes if you copy this back into your formula directly.

View original
Did this topic help you find an answer to your question?

2 replies

C
Forum|alt.badge.img+2
  • Chris-T
  • Inspiring
  • 29 replies
  • Answer
  • July 1, 2021

I think you need to sum the IFs as individual statements, not nested.

IF({Line Item #1 Coffee}=“Espresso Blend”, {Total Coffee Weight (G): Line Item #1 Quantity},0)
+
IF({Line Item #2 Coffee}=“Espresso Blend”, {Total Coffee Weight (G): Line Item #2 Quantity},0)
+
IF({Line Item #3 Coffee}=“Espresso Blend”, {Total Coffee Weight (G): Line Item #3 Quantity},0)
+
IF({Line Item #4 Coffee}=“Espresso Blend”, {Total Coffee Weight (G): Line Item #4 Quantity},0)

Of course, look out for your double quotes if you copy this back into your formula directly.

J
1 person likes this
  • J
    J_Stan
J
Forum|alt.badge.img+4
  • J_Stan
  • Author
  • New Participant
  • 1 reply
  • July 1, 2021
Chris-T wrote:

I think you need to sum the IFs as individual statements, not nested.

IF({Line Item #1 Coffee}=“Espresso Blend”, {Total Coffee Weight (G): Line Item #1 Quantity},0)
+
IF({Line Item #2 Coffee}=“Espresso Blend”, {Total Coffee Weight (G): Line Item #2 Quantity},0)
+
IF({Line Item #3 Coffee}=“Espresso Blend”, {Total Coffee Weight (G): Line Item #3 Quantity},0)
+
IF({Line Item #4 Coffee}=“Espresso Blend”, {Total Coffee Weight (G): Line Item #4 Quantity},0)

Of course, look out for your double quotes if you copy this back into your formula directly.


Works perfectly! Thanks @Chris-T - I thought it would be something straightforward that I was missing. Appreciate the help.

C
1 person likes this
  • C
    Chris-T

Reply


Most helpful members this week

Terms & Conditions