Help

Adding together numeric values based on If Formula result

Topic Labels: Formulas
Solved
Jump to Solution
995 2
cancel
Showing results for 
Search instead for 
Did you mean: 
J_Stan
4 - Data Explorer
4 - Data Explorer

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

))))

1 Solution

Accepted Solutions
Chris-T
6 - Interface Innovator
6 - Interface Innovator

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.

See Solution in Thread

2 Replies 2
Chris-T
6 - Interface Innovator
6 - Interface Innovator

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.